Home > Database > Mysql Tutorial > body text

Why Does My Query Use Temporary Tables and Filesort?

Susan Sarandon
Release: 2024-11-05 01:15:02
Original
230 people have browsed it

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;
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!