Why Does My MySQL Query Cause Temporary Tables and Filesort?

Trouble with Temporary and Filesort in MySQL: A Schema and Query Analysis

In an event tracking system with lookup tables and a logging table, an issue arises with a query intended to display object statistics in descending order of importance. While the query functions correctly with a small number of entries, its EXPLAIN results suggest potential for optimization.

The query in question utilizes a left join between the event_log and lookup_event_objects tables, grouping results by object_id and sorting by count_rows in descending order, followed by object_desc in ascending order. However, this query generates warnings about using a temporary table and filesort.

MySQL employs temporary tables when the query contains an ORDER BY clause different from the GROUP BY clause, or when sorting or grouping is referenced from tables other than the first one in the join. In this case, sorting is based on a computed field, making index usage impossible, hence the use of filesort.

To address the issue, remove the ORDER BY clause for leo.object_desc. This will eliminate the need for temporary tables and filesort, improving query performance:

<code class="sql">EXPLAIN SELECT 
            el.object_id, 
            leo.object_desc, 
            COUNT(el.object_id) as count_rows
        FROM 
            event_log el 
            LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
        GROUP BY 
            el.object_id
        ORDER BY 
            count_rows DESC;</code>

The above is the detailed content of Why Does My MySQL Query Cause Temporary Tables and Filesort?. For more information, please follow other related articles on the PHP Chinese website!