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.
[URL]MySQL :: MySQL Forums :: Performance
[URL]MySQL :: MySQL Forums :: Optimizer & Parser
Toasty
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 )
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.
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.