lingo lingo - 1 year ago 69
MySQL Question

Indexing slow MySQL query with LEFT JOINS and CASE

I have this MySQL query so slow:

CASE WHEN o.created > a.starting THEN o.created ELSE a.starting END AS lastact
FROM customer c
LEFT JOIN orders o ON o.customerId = c.customerId
LEFT JOIN activities a ON a.customerId = c.customerId
AND = 1
AND c.customerSubgroup NOT LIKE 'DEL%'
GROUP BY c.customerId
ORDER BY lastact ASC, name ASC

I have a lot of data in activity and orders. I need some help with indexing those tables (think that it's the biggest problem). Now I have primary key in all columns which are mentioned in the query...

Thanks for advance.

Answer Source

Having an index on just the primary key is not going to do it for you. You should build indexes that help based on the criteria you are working to extract. Covering indexes for your orders, activities so it does not have to go back to the raw data pages, but gets its results from the index directly.

table      index
customer   ( active, area, customerSubGroup, customerID )
orders     ( customerID, created )
activities ( customerID, starting )
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download