Master MySQL Sorting: Index vs Filesort and How to Optimize Queries

Reference Answer

MySQL data sorting is mainly achieved by index sorting and filesort.

Index Sorting

Index sorting is the most efficient method. When ORDER BY fields match an index and the order aligns, MySQL uses the index order directly, avoiding extra sorting and no "Using filesort" appears.

Filesort

If an index cannot be used, MySQL triggers filesort. The strategy depends on data size:

Memory sort phase: if data fits into sort_buffer (controlled by sort_buffer_size), sorting occurs in memory. Two modes: single‑row sort (all fields) and double‑row sort (rowid sort) when rows are large.

Disk sort phase: when data exceeds sort_buffer, MySQL performs merge sort, writing temporary files and merging them, which incurs heavy I/O.

Key factors for choosing the method include presence of suitable indexes, data volume, sort_buffer_size, and max_length_for_sort_data parameters.

Scenario

Example: an e‑commerce order query retrieving the latest 20 orders for a user.

SELECT order_id, user_id, order_time, total_amount
FROM orders
WHERE user_id = 10086
ORDER BY order_time DESC
LIMIT 20;

The EXPLAIN may show either a clean index scan or "Using filesort", leading to large performance differences.

Index Sorting Details

2.1 Index is naturally ordered

In InnoDB, indexes use a B+ tree; leaf nodes form an ordered list, so an index on order_time already provides a time‑sorted catalog.

2.2 Conditions for index sorting

1. Index column order must exactly match ORDER BY order.

Example composite index INDEX idx_user_time(user_id, order_time) works for WHERE user_id = 10086 ORDER BY order_time but not for ORDER BY order_time, user_id.

2. Sorting direction must be consistent.

Before MySQL 8.0, mixed directions could not use the index; MySQL 8.0+ supports descending indexes and can scan indexes in reverse.

2.3 Optimizer trade‑offs

The optimizer may still avoid index sorting if the cost of row lookups is high.

Filesort Details

3.1 When filesort triggers

Sorting column lacks an index.

Index cannot cover all selected columns (requires many row lookups).

ORDER BY uses expressions or functions.

Complex multi‑table joins.

Optimizer estimates index sorting cost too high.

When any of these occur, the Extra column shows "Using filesort".

3.2 sort_buffer

MySQL allocates a per‑session sort_buffer (default 256KB, controlled by sort_buffer_size) for sorting. Large numbers of concurrent connections multiply memory usage.

3.3 Single‑row sort

All required fields are loaded into sort_buffer, sorted, and results returned without further lookups. Good for small rows but memory‑intensive.

3.4 Double‑row sort (rowid sort)

If a row exceeds max_length_for_sort_data (default 4096 bytes), MySQL reads only the sorting column and primary key into the buffer, sorts, then fetches remaining columns from the table. This reduces memory usage at the cost of extra lookups for the final rows.

Practical Tips

Create appropriate indexes that match WHERE and ORDER BY clauses.

Consider descending indexes in MySQL 8.0+ for mixed directions.

Adjust sort_buffer_size and max_length_for_sort_data to keep sorting in memory when possible.

Use covering indexes to avoid costly row lookups.