I created an innocent looking MySQL query which eventually took up to 10 minutes to complete. I am posting this here so you don't have to make the same stupid mistake as I did.
Code:
SELECT report_dt, ps, SUBTIME(report_dt, "04:00") AS local_dt FROM \
pms_meas_log WHERE SUBTIME(report_dt, "04:00") < "2016-11-26 23:59" AND \
dev = "h19_pm" ORDER BY report_dt LIMIT 1;
The table is a simple linear table. report_dt is DATETIME. The table contains 9.2 x 10e6 records. report_dt is the primary index.
All times are recorded in UTC. But the client is in UTC-4. So in the select statement local_dt is an alias for SUBTIME(report_dt, "04:00"). So UTC-4.
The problem is in this part:
Code:
WHERE SUBTIME(report_dt, "04:00") < "2016-11-26"
One should not have such a function call (or any calculation for that matter) in the WHERE part of a query. MySQL will calculate this value for all 9.2 million records.
This query was created by a model in a PHP framework. The model constructed the query faithfully and as efficient as could be expected. I only should not have used that alias in the WHERE clause. Once I used the plain report_dt in the WHERE clause the query run in less than 7 seconds.
jlinkels
