Since I recently posted a plea for MySQL query optimization help, I've been reading like mad about how to optimize MySQL queries. I'm not perfect, but I'm getting the hang of it. I have one query which used to take about 15 minutes to run now taking about five seconds. It didn't help that the query has hideously complicated logic with a bizarre edge case (I've changed all of the table and column names):
SELECT DATE_FORMAT( m1.processed, '%Y-%m') as `When`,
m1.aid AS 'A ID',
c.fname AS 'First Name',
c.sname AS 'Last Name',
SUM(
( m1.from_foo + m1.from_bar )
-
( m1.to_foo + m1.to_bar )
) AS Total,
IF (a.extra IS NULL, 'Yes', 'No') AS Disabled
FROM manifests m1
LEFT JOIN regulators ON m1.manifest = r_manifest
LEFT JOIN manifests m2 ON m2.manifest = o_manifest
LEFT JOIN alligator_crutches ac ON b.dedid = ac.dedid
INNER JOIN alligators a ON a.aid = m1.aid
INNER JOIN customers c ON c.aid = m1.aid
INNER JOIN baubles b ON m1.manifest = b.manifest
WHERE m1.aid != 0
AND m1.aid IS NOT NULL
AND m1.processed BETWEEN '2007-08-01' AND '2007-08-31'
AND (
m2.processed IS NULL
OR
(
m1.manifest = r_manifest
AND
m2.processed BETWEEN '2007-08-01' AND '2007-08-31'
)
)
GROUP BY m1.aid
ORDER BY Total DESC
All things considered, I'm feeling quite accomplished. It's not perfect, but it's a darn good start.