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?
I’m sure if you post the details here someone will help you.
The performance/optimisation forums on the mysql site are usually a good place to post these kinds of things too.
Right. Please send EXPLAIN with and without ORDER BY.
It is easy to come up with query where order by would make it run 1000 times longer and more )
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
(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.
I can’t tell you much without EXPLAIN.
What do you mean by “times out” - run it from standard mysql command line client it should not be that long and it should not time out even if EXPLAIN would get hours to run.
the problem may be in the tables without primary key.
check if there are repeating rows in those tables.
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.