order by slowing down query

[krisc13]

I have the following select statement:

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_viewe d) 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.

When ORDER BY is removed from this statement it runs very fast. It is running on a database with over 500k customers in both the person and customer tables. It seems to only hang when ORDER BY is added. Any suggestions would be helpful. Thanks!

[miller]

I have the following select statement:

SELECT DISTINCT
cust_id,
FROM person
JOIN customer
ON cust_person_id = person_id
...
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.

When ORDER BY is removed from this statement it runs very fast. It is running on a database with over 500k customers in both the person and customer tables. It seems to only hang when ORDER BY is added. Any suggestions would be helpful. Thanks!

I would look into adding an index for "cust_lead_date". This is not garanteed to work given that you're doing a "DISTINCT" call, but it would be worth trying.

ALTER TABLE customer ADD INDEX lead_date (cust_lead_date);

It will take a while to create the index, just as it took a while to run the query with the "ORDER BY". But any subsequent select statement should speed up by an order of magnitude.

Note: Your "WHERE" clause currently is rather strange as it will pull every record: LIKE '%%%' .