Using filesort in MySQL EXPLAIN

What it does

Using filesort in the Extra column means MySQL sorts the rows itself to satisfy ORDER BY, after reading them, instead of reading them already in order from an index. It collects the sort key (and either the needed columns or a pointer to the row) for every row that passes the WHERE clause, sorts that, then returns rows in the new order.

Despite the name, it doesn’t necessarily touch a file. The sort runs in a buffer of up to sort_buffer_size (256 KB by default) and spills to temporary files only when the data doesn’t fit.

In EXPLAIN ANALYZE and EXPLAIN FORMAT=TREE the same step shows as a Sort: line:

-> Sort: orders.total DESC, limit input to 20 row(s) per chunk

When the planner picks it

Whenever no index can deliver rows in the requested order. An index on (a, b) returns rows sorted by a, then b, so it can satisfy ORDER BY a, b, or WHERE a = ? ORDER BY b. It can’t satisfy:

• ORDER BY on a column that isn’t in the index being used, or isn’t next in line after the columns compared with =
• WHERE a IN (…) ORDER BY b or WHERE a > ? ORDER BY b: rows are sorted by b only within each value of a
• ORDER BY on an expression (ORDER BY ABS(x)), on columns from more than one table of a join, or a mix of ASC and DESC that the index wasn’t defined with
• GROUP BY and ORDER BY on different columns (usually with Using temporary)

The optimiser may also choose a filesort on purpose when using the ordered index would mean many random row lookups: sorting the result of a table scan can be cheaper.

Reading its numbers

In traditional EXPLAIN, Using filesort sits on the row of the table being sorted, and that row’s rows × filtered is roughly how many rows go into the sort. EXPLAIN ANALYZE is more useful:

• The rows= on the line under Sort: is how many rows were sorted.
• limit input to N row(s) per chunk means a LIMIT lets MySQL keep only the best N rows while reading (a top-N sort), so the sort itself stays small. Reading all the input still costs time.
• The actual time of the Sort: line minus that of its input is the time spent sorting.

To see whether a sort went to disk, compare your session’s Sort_merge_passes before and after the query (SHOW SESSION STATUS LIKE 'Sort%'). Any increase means the sort didn’t fit in sort_buffer_size.

When it’s a problem

A filesort of 25 rows costs nothing; leave it. It’s worth fixing when:

• Many rows are sorted to return a few. ORDER BY … LIMIT 20 over 50,000 matching rows still reads and ranks all 50,000. An index that matches the WHERE and the ORDER BY lets MySQL read 20 rows and stop.
• The sort spills to disk, visible as a rising Sort_merge_passes. Deep OFFSET pagination is a common cause, since MySQL must sort OFFSET + LIMIT rows.
• The query runs constantly, so a modest sort adds up.

The usual fix is a composite index with the equality columns first and the ORDER BY columns next, in the same direction (or all reversed; MySQL can read an index backwards). Raising sort_buffer_size for one session can help a big report, but it doesn’t remove the work.

Example

On MySQL 8.4.11, an orders table with 500,000 rows:

CREATE TABLE orders (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id int NOT NULL,
  status varchar(20) NOT NULL,
  total decimal(10,2) NOT NULL,
  created_at datetime NOT NULL,
  note varchar(100) NULL,
  KEY idx_customer (customer_id),
  KEY idx_status_created (status, created_at)
);
-- 500,000 rows; 10% have status 'refunded'

The 20 largest refunds:

EXPLAIN SELECT * FROM orders WHERE status = 'refunded' ORDER BY total DESC LIMIT 20;

+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+-------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows  | filtered | Extra          |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+-------+----------+----------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_status_created | idx_status_created | 82      | const | 92148 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+-------+----------+----------------+

EXPLAIN ANALYZE shows all 50,000 refunded orders being fetched and ranked to return 20:

-> Limit: 20 row(s)  (cost=10345 rows=20) (actual time=133..133 rows=20 loops=1)
    -> Sort: orders.total DESC, limit input to 20 row(s) per chunk  (cost=10345 rows=92148) (actual time=133..133 rows=20 loops=1)
        -> Index lookup on orders using idx_status_created (status='refunded')  (cost=10345 rows=92148) (actual time=1.73..124 rows=50000 loops=1)

An index that starts with the WHERE column and continues with the ORDER BY column:

CREATE INDEX idx_status_total ON orders (status, total);

+----+-------------+--------+------------+------+-------------------------------------+------------------+---------+-------+-------+----------+---------------------+
| id | select_type | table  | partitions | type | possible_keys                       | key              | key_len | ref   | rows  | filtered | Extra               |
+----+-------------+--------+------------+------+-------------------------------------+------------------+---------+-------+-------+----------+---------------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_status_created,idx_status_total | idx_status_total | 82      | const | 92148 |   100.00 | Backward index scan |
+----+-------------+--------+------------+------+-------------------------------------+------------------+---------+-------+-------+----------+---------------------+

-> Limit: 20 row(s)  (cost=10345 rows=20) (actual time=2.25..2.27 rows=20 loops=1)
    -> Index lookup on orders using idx_status_total (status='refunded') (reverse)  (cost=10345 rows=92148) (actual time=2.22..2.23 rows=20 loops=1)

No sort, 20 rows read, 133 ms down to 2.3 ms. The rows estimate didn’t change: it’s the number of matching rows, not the number read before LIMIT stops.

The same index doesn’t help with two statuses, because status IN (…) gives two runs of the index, each sorted on its own. The optimiser fell back to a table scan and a filesort:

EXPLAIN ANALYZE SELECT id, customer_id, status, total FROM orders
WHERE status IN ('refunded', 'cancelled') ORDER BY total DESC LIMIT 20;

-> Limit: 20 row(s)  (cost=50266 rows=20) (actual time=152..152 rows=20 loops=1)
    -> Sort: orders.total DESC, limit input to 20 row(s) per chunk  (cost=50266 rows=498888) (actual time=152..152 rows=20 loops=1)
        -> Filter: (orders.`status` in ('refunded','cancelled'))  (cost=50266 rows=498888) (actual time=0.444..136 rows=100000 loops=1)
            -> Table scan on orders  (cost=50266 rows=498888) (actual time=0.416..89.7 rows=500000 loops=1)

Deep pagination spilled to disk. Without the new index, SHOW SESSION STATUS LIKE 'Sort%' before and after SELECT * FROM orders WHERE status = 'refunded' ORDER BY total DESC, id LIMIT 20 OFFSET 40000:

| Sort_merge_passes | 0     |
| Sort_rows         | 20    |
…
| Sort_merge_passes | 1     |
| Sort_rows         | 40040 |

That query sorted 40,020 rows (OFFSET + LIMIT) and needed a merge pass on disk; the 20 earlier rows were from the previous query in the same session.

In Inlet

Run EXPLAIN or EXPLAIN ANALYZE in Inlet’s query editor like any other statement (⌘↩). With your own Anthropic API key, Ask Claude (⌘L) can explain a plan; it sends the schema and the plan, never rows. The structure editor shows the CREATE INDEX statement before it runs.