How Indexes Eliminate Filesort: Optimizing ORDER BY in SQL
This article explains why MySQL ORDER BY may trigger a filesort when no suitable index exists, demonstrates the issue with a product query, and shows how adding a composite index on category_id and price lets the database return rows already sorted, removing the need for an extra sorting step.
In MySQL, ORDER BY can be executed in two ways: using a covering index, which returns rows already sorted, or without an index, which requires a temporary table and a filesort.
Example: a product table where we want items of a specific category sorted by price. The query SELECT * FROM product WHERE category_id = N ORDER BY price only has an index on category_id, so MySQL performs a filesort, as shown by EXPLAIN with Extra: Using where; Using filesort.
By adding a composite index on (category_id, price), the same query’s EXPLAIN output changes to Extra: Using where without filesort, because the rows are retrieved in the desired order directly from the index.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
Java High-Performance Architecture
Sharing Java development articles and resources, including SSM architecture and the Spring ecosystem (Spring Boot, Spring Cloud, MyBatis, Dubbo, Docker), Zookeeper, Redis, architecture design, microservices, message queues, Git, etc.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.