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