How to Avoid MySQL Filesort with Indexing

In database optimization, a “filesort” operation occurs when MySQL must perform an extra sorting pass on the retrieved rows to satisfy an ORDER BY or GROUP BY clause. This article explains the underlying causes of filesort operations, why they can degrade query performance, and how you can strategically design indexes to avoid them entirely by utilizing index-driven sorting.

What is a MySQL Filesort?

Despite its name, a filesort does not necessarily mean that sorting happens on disk. It is a sorting algorithm that MySQL uses when it cannot use an index to retrieve data in the requested order.

If the dataset to be sorted fits into the memory allocated by the sort_buffer_size system variable, the filesort is executed entirely in RAM. If the dataset is too large, MySQL splits the data into smaller chunks, sorts them in memory, and merges them on disk. Regardless of whether it occurs in memory or on disk, a filesort consumes significant CPU and memory resources.

Common Causes of Filesort Operations

MySQL relies on filesort whenever the index structure cannot guarantee the sorted order required by your query. The most common triggers include:

• Missing Indexes: The query contains an ORDER BY or GROUP BY clause on columns that are not indexed.
• Incorrect Composite Index Order: A composite index exists, but the query’s ORDER BY columns do not align with the leftmost prefix of the index.
• Mixed Sorting Directions: Sorting by multiple columns in different directions (e.g., ORDER BY col1 ASC, col2 DESC) on MySQL versions prior to 8.0, or without a descending index.
• Sorting Joins on Different Tables: Sorting by a column from a table other than the first non-constant table in the join order.
• Using Functions on Sort Columns: Applying functions, calculations, or type conversions to the column in the ORDER BY clause (e.g., ORDER BY YEAR(created_at)).

How to Prevent Filesort Using Indexes

To avoid filesort, you must structure your indexes so that MySQL can read the rows in the exact order the index already stores them.

1. Leverage the Leftmost Prefix Rule

When a query contains both a WHERE clause and an ORDER BY clause, you should create a composite index that covers both. The columns in the WHERE clause must come first in the index, followed immediately by the columns in the ORDER BY clause.

For example, consider this query:

SELECT * FROM orders WHERE customer_id = 101 ORDER BY order_date DESC;

To avoid a filesort, create a composite index on both columns in this specific order:

CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

MySQL will use the index to filter by customer_id and, because the index stores order_date sequentially within each customer_id, it can return the rows in the correct order without sorting them.

2. Match the Sorting Direction

MySQL 8.0 and later support descending indexes. If you need to sort multiple columns in different directions, you must define the index with the matching directions.

For this query:

SELECT * FROM users ORDER BY points DESC, username ASC;

You should create the following index:

CREATE INDEX idx_points_username ON users (points DESC, username ASC);

3. Avoid Sorting Across Different Tables

If you must sort the results of a table join, ensure the ORDER BY clause only references columns from the first table in the join optimizer’s execution path. If you sort on columns from different tables, MySQL will always perform a filesort.

How to Verify if Your Query Uses Filesort

You can check if a query triggers a filesort by prepending the EXPLAIN keyword to your SQL statement:

EXPLAIN SELECT * FROM orders WHERE customer_id = 101 ORDER BY order_date DESC;

Look at the Extra column in the output. If you see “Using filesort”, MySQL is performing an extra sorting pass. If you have successfully optimized the query with the correct index, “Using filesort” will disappear, indicating that MySQL is reading the sorted data directly from the index.