Home » Performance » MySQL » ORDER BY slowing down query
| ORDER BY slowing down query [message #271] |
Fri, 20 October 2006 15:55  |
 krisc13
Messages: 4
Registered: October 2006 |
Junior Member |
|
|
|
I have a large select statement that runs fine until the ORDER BY clause is added. The order by is crucial but makes the page take over 10 minutes to load and it never finishes. May I paste the statement here and perhaps get some advice?
|
|
| | |
| Re: ORDER BY slowing down query [message #277 is a reply to message #271 ] |
Mon, 23 October 2006 08:34  |
krisc13
Messages: 4
Registered: October 2006 |
Junior Member |
|
|
SELECT DISTINCT
cust_id,
cust_priority priority,
person_name_first f_name,
person_name_last l_name,
person_email_primary email,
person_address_a address,
person_address_a2 address_2,
person_address_a_city city,
person_address_a_zip zip,
person_address_a_state state,
customer__group.cust_group_id group_id,
customer__group.cust_group_shared_by shared_by,
customer__user.cust_user_id owner_id,
UNIX_TIMESTAMP(cust_lead_date) lead_date,
UNIX_TIMESTAMP(customer__user.cust_user_date_added) date_added,
UNIX_TIMESTAMP(customer__user.cust_user_date_viewed) date_viewed,
UNIX_TIMESTAMP(customer__user.cust_user_refer_date_in) date_refered_in,
UNIX_TIMESTAMP(customer__user.cust_user_refer_date_out) date_refered_out,
customer__user.cust_user_refered_by refered_by,
customer__user.cust_user_refered_to refered_to,
customer__user.cust_user_id cust_user
FROM person
JOIN customer
ON cust_person_id = person_id
LEFT JOIN customer__property
ON cust_prop_cust_id = cust_id
LEFT JOIN property
ON prop_id = cust_prop_prop_id
LEFT JOIN customer__group
ON cust_group_cust_id = cust_id
AND cust_group_group_id = 1
LEFT JOIN customer__user
ON cust_user_cust_id = cust_id
AND cust_user_user_id = 46
WHERE
(customer__group.cust_group_id IS NOT NULL OR customer__user.cust_user_id IS NOT NULL)
ORDER BY customer.cust_lead_date DESC, f_name ASC LIMIT 0,16.
Explain times out when we run it on this query. When we run this query without the ORDER BY it runs very fast.
|
|
| |
| Re: ORDER BY slowing down query [message #279 is a reply to message #278 ] |
Mon, 23 October 2006 08:42 |
krisc13
Messages: 4
Registered: October 2006 |
Junior Member |
|
|
Okay my coworker was able to run explain...
EXPLAIN SELECT
DISTINCT cust_id,
cust_priority priority,
person_name_first f_name,
person_name_last l_name,
person_email_primary email,
person_address_a address,
person_address_a2 address_2,
person_address_a_city city,
person_address_a_zip zip,
person_address_a_state state,
customer__group.cust_group_id group_id,
customer__group.cust_group_shared_by shared_by,
customer__user.cust_user_id owner_id,
UNIX_TIMESTAMP(cust_lead_date) lead_date,
UNIX_TIMESTAMP(customer__user.cust_user_date_added) date_added,
UNIX_TIMESTAMP(customer__user.cust_user_date_viewed) date_viewed,
UNIX_TIMESTAMP(customer__user.cust_user_refer_date_in) date_refered_in,
UNIX_TIMESTAMP(customer__user.cust_user_refer_date_out) date_refered_out,
customer__user.cust_user_refered_by refered_by,
customer__user.cust_user_refered_to refered_to,
customer__user.cust_user_id cust_user
FROM person
JOIN customer
ON cust_person_id = person_id
LEFT JOIN customer__property
ON cust_prop_cust_id = cust_id
LEFT JOIN property
ON prop_id = cust_prop_prop_id
LEFT JOIN customer__group
ON cust_group_cust_id = cust_id AND cust_group_group_id = 1
LEFT JOIN customer__user
ON cust_user_cust_id = cust_id AND cust_user_user_id = 46
WHERE
(customer__group.cust_group_id IS NOT NULL OR customer__user.cust_user_id IS NOT NULL)
AND
(person_name_first LIKE '%%%' OR person_name_last LIKE '%%%' OR CONCAT(person_name_first,' ',person_name_last) LIKE '%%%')
ORDER BY customer.cust_lead_date DESC, f_name ASC
LIMIT 0,16;
1, 'SIMPLE', 'customer', 'ALL', 'cust_lookup', '', '', '', 562170, 'Using temporary; Using filesort'
1, 'SIMPLE', 'customer__property', 'ref', 'cust_prop_cust_id', 'cust_prop_cust_id', '4', 'f_agent.customer.cust_id', 1, 'Using index'
1, 'SIMPLE', 'property', 'eq_ref', 'PRIMARY,prop_id', 'PRIMARY', '4', 'f_agent.customer__property.cust_prop_prop_id', 1, 'Using index'
1, 'SIMPLE', 'customer__group', 'ref', 'cust_group_group_id', 'cust_group_group_id', '8', 'const,f_agent.customer.cust_id', 12, ''
1, 'SIMPLE', 'customer__user', 'ref', 'cust_user_cust_id', 'cust_user_cust_id', '9', 'f_agent.customer.cust_id,const', 18, 'Using where'
1, 'SIMPLE', 'person', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'f_agent.customer.cust_person_id', 1, 'Using where'
|
|
|
| Re: ORDER BY slowing down query [message #280 is a reply to message #279 ] |
Mon, 23 October 2006 08:44 |
krisc13
Messages: 4
Registered: October 2006 |
Junior Member |
|
|
Here are the others:
EXPLAIN SELECT
DISTINCT cust_id,
cust_priority priority,
person_name_first f_name,
person_name_last l_name,
person_email_primary email,
person_address_a address,
person_address_a2 address_2,
person_address_a_city city,
person_address_a_zip zip,
person_address_a_state state,
customer__group.cust_group_id group_id,
customer__group.cust_group_shared_by shared_by,
customer__user.cust_user_id owner_id,
UNIX_TIMESTAMP(cust_lead_date) lead_date,
UNIX_TIMESTAMP(customer__user.cust_user_date_added) date_added,
UNIX_TIMESTAMP(customer__user.cust_user_date_viewed) date_viewed,
UNIX_TIMESTAMP(customer__user.cust_user_refer_date_in) date_refered_in,
UNIX_TIMESTAMP(customer__user.cust_user_refer_date_out) date_refered_out,
customer__user.cust_user_refered_by refered_by,
customer__user.cust_user_refered_to refered_to,
customer__user.cust_user_id cust_user
FROM person
JOIN customer
ON cust_person_id = person_id
LEFT JOIN customer__property
ON cust_prop_cust_id = cust_id
LEFT JOIN property
ON prop_id = cust_prop_prop_id
LEFT JOIN customer__group
ON cust_group_cust_id = cust_id AND cust_group_group_id = 1
LEFT JOIN customer__user
ON cust_user_cust_id = cust_id AND cust_user_user_id = 46
WHERE
(customer__group.cust_group_id IS NOT NULL OR customer__user.cust_user_id IS NOT NULL)
LIMIT 0,16;
1, 'SIMPLE', 'customer', 'ALL', 'cust_lookup', '', '', '', 562173, 'Using temporary'
1, 'SIMPLE', 'customer__property', 'ref', 'cust_prop_cust_id', 'cust_prop_cust_id', '4', 'f_agent.customer.cust_id', 1, 'Using index'
1, 'SIMPLE', 'property', 'eq_ref', 'PRIMARY,prop_id', 'PRIMARY', '4', 'f_agent.customer__property.cust_prop_prop_id', 1, 'Using index'
1, 'SIMPLE', 'customer__group', 'ref', 'cust_group_group_id', 'cust_group_group_id', '8', 'const,f_agent.customer.cust_id', 12, ''
1, 'SIMPLE', 'customer__user', 'ref', 'cust_user_cust_id', 'cust_user_cust_id', '9', 'f_agent.customer.cust_id,const', 18, 'Using where'
1, 'SIMPLE', 'person', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'f_agent.customer.cust_person_id', 1, 'Using where'
|
|
|
| Re: ORDER BY slowing down query [message #291 is a reply to message #280 ] |
Wed, 25 October 2006 11:45 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
Interesting, explains are similar besides ORDER BY, which however should not make that difference as thee is group by (distinct) before it.
Could be explain is misleading in this case.
I would also suggest you to profile query by runnin at Handler_xxx increments during statement execution this is good way to check what was really happening.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
| |
| Re: ORDER BY slowing down query [message #991 is a reply to message #271 ] |
Mon, 26 March 2007 09:54 |
sterin
Messages: 324
Registered: March 2007
Location: Sweden |
Senior Member |
|
|
How many MB is the result set from this query?
And what is your sort_buffer_size set to?
Usually when an order by takes a really long time the sort_buffer_size is to small and that forces mysql to write the temporary table to disk which slows down things a _lot_.
Since you have LEFT JOIN and you are ordering on two columns that are not in the same table I don't think that we can avoid the temporary table for the order by in the end.
The only thing we can do is to increase the buffer size to perform the sort faster.
The other possibility you have is if you can narrow down the search criteria in the conditions so that there are fewer rows to sort.
|
|
|
Goto Forum:
Current Time: Mon Jul 6 13:48:26 EDT 2009
Total time taken to generate the page: 0.02248 seconds |
Members
Search
Help
Register
Login
Home
