Why Do MySQL Queries Show \'Using Temporary\' and \'Using Filesort\' and How Can You Optimize Them?

MySQL Optimization: Understanding "Using index, using temporary, using filesort"

When executing a query, MySQL may resort to using temporary tables and filesort, as indicated by the messages "Using temporary" and "Using filesort." Let's explore what this means in the context of a specific query:

EXPLAIN SELECT
    el.object_id,
    leo.object_desc,
    COUNT(el.object_id) AS count_rows
FROM
    event_log AS el
LEFT JOIN
    lookup_event_objects AS leo ON leo.object_id = el.object_id
GROUP BY
    el.object_id
ORDER BY
    count_rows DESC,
    leo.object_desc ASC

"Using temporary"

According to MySQL documentation, temporary tables are created when:

• There is an ORDER BY clause and a different GROUP BY clause, or the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

In the given query, the ORDER BY clause sorts by a computed field "count_rows," which is not in the GROUP BY clause, and the GROUP BY clause uses a column from the joined table "lookup_event_objects." Therefore, MySQL creates a temporary table to perform the grouping and sorting.

"Using filesort"

MySQL uses filesort when the sort operation cannot be performed using an index. In this query, the sort on the computed field "count_rows" cannot be indexed, so MySQL uses filesort to perform the sorting.

Optimization

To optimize this query, you can create an index on "count_rows." However, since it is a computed field, you will need to use a MySQL trick:

CREATE TABLE lookup_event_objects_temp AS
SELECT
    object_id,
    object_desc,
    COUNT(DISTINCT event_id) AS count_rows
FROM
    event_log
GROUP BY
    object_id;

CREATE INDEX idx_count_rows ON lookup_event_objects_temp (count_rows);

ALTER TABLE lookup_event_objects_temp RENAME TO lookup_event_objects;

By creating this temporary table with an indexed "count_rows" field, the query can use the index to efficiently perform the sorting, eliminating the need for filesort and reducing the temporary table usage.

The above is the detailed content of Why Do MySQL Queries Show \'Using Temporary\' and \'Using Filesort\' and How Can You Optimize Them?. For more information, please follow other related articles on the PHP Chinese website!