lingo lingo - 5 months ago 8
MySQL Question

Indexing slow MySQL query with LEFT JOINS and CASE

I have this MySQL query so slow:

SELECT
c.customerId, c.name,
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
WHERE c.area IN ('AREA I', 'AREA II', 'AREA III')
AND c.active = 1
AND c.customerSubgroup NOT LIKE 'DEL%'
GROUP BY c.customerId
ORDER BY lastact ASC, name ASC
LIMIT 15


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

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 )
Comments