Why Does My Query Use Temporary Tables and Filesort?

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!