Why MySQL Uses Temporary and Filesort in This Query
Your query for extracting object statistics from event_log and lookup_event_objects tables is facing a performance bottleneck, as indicated by the EXPLAIN output "Using index; Using temporary; Using filesort." Here's why:
Temporary Tables
MySQL uses temporary tables when there's a mismatch between the ORDER BY and GROUP BY clauses, or when the ordering or grouping columns belong to non-joined tables. In this case, your query suffers from the first condition, as the ORDER BY clause (count_rows DESC, leo.object_desc ASC) differs from the GROUP BY clause (el.object_id).
Filesort
"Using filesort" indicates that MySQL is unable to perform the sorting specified in the ORDER BY clause using an index. Since you're ordering by a computed field (count_rows), the database needs to perform a complete table scan and sort the results.
Possible Solution
As suggested in the provided answer, the solution is to remove the discrepancy between the ORDER BY and GROUP BY clauses. You could replace the count_rows alias with the actual count operation within the GROUP BY clause:
SELECT el.object_id, leo.object_desc, COUNT(el.object_id) AS count FROM event_log el LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id GROUP BY el.object_id, count ORDER BY count DESC, leo.object_desc ASC;
This will eliminate the need for temporary tables and filesort, potentially improving the query's performance.
The above is the detailed content of Why Does My Query Use Temporary Tables and Filesort?. For more information, please follow other related articles on the PHP Chinese website!