Skip to main content
MySQL · lesson 2 of 8

Beginner

1

Intermediate

6

Advanced

1

Also part of MySQL

1
Open the MySQL page →
MySQL intermediate Lesson 2 of 8

Also part of: Databases

Composite Indexes: The Filesort a Single-Column Index Can't Avoid

Filtering by genre and sorting by year: a single-column index still needs a manual sort afterward (2.87ms). A composite index on both columns delivers pre-sorted results — measured 21x faster, and the Sort step vanishes from the plan entirely.

By SkillByExample · Published

mysql indexing composite-index filesort explain

Filtering by one column and sorting by another can defeat a perfectly good index on the filtered column alone — the sort still has to happen somewhere.

The bug: an index that can’t avoid the sort

EXPLAIN SELECT * FROM books WHERE genre = 'SciFi' ORDER BY published_year LIMIT 10;
id  select_type  table  partitions  type  possible_keys    key              key_len  ref    rows  filtered  Extra
1   SIMPLE       books  NULL        ref   idx_books_genre  idx_books_genre  203      const  834   100.00    Using filesort

idx_books_genre (from the previous lesson) finds the 834 SciFi rows efficiently — but Using filesort in Extra means MySQL then has to sort those 834 rows by published_year itself, since the index only guarantees they’re grouped by genre, not ordered within it.

EXPLAIN ANALYZE SELECT * FROM books WHERE genre = 'SciFi' ORDER BY published_year LIMIT 10;
-> Limit: 10 row(s)  (cost=99.2 rows=10) (actual time=2.86..2.87 rows=10 loops=1)
    -> Sort: books.published_year, limit input to 10 row(s) per chunk  (cost=99.2 rows=834) (actual time=2.84..2.84 rows=10 loops=1)
        -> Index lookup on books using idx_books_genre (genre='SciFi')  (cost=99.2 rows=834) (actual time=0.197..1.13 rows=834 loops=1)

A real, separate Sort step — actual time=2.84ms for the sort alone, on top of 1.13ms to fetch the rows in the first place.

The fix: a composite index in the same order as the query

CREATE INDEX idx_genre_year ON books(genre, published_year);
EXPLAIN ANALYZE SELECT * FROM books WHERE genre = 'SciFi' ORDER BY published_year LIMIT 10;
-> Limit: 10 row(s)  (cost=99.2 rows=10) (actual time=0.134..0.137 rows=10 loops=1)
    -> Index lookup on books using idx_genre_year (genre='SciFi')  (cost=99.2 rows=834) (actual time=0.133..0.135 rows=10 loops=1)

The Sort step is gone entirely — 0.137ms total, versus 2.87ms before, a measured ~21x improvement. idx_genre_year stores rows sorted by genre first and published_year second, so the SciFi rows are already sitting in year order inside the index itself; MySQL reads the first 10 straight off the index with no separate sort needed at all.

Column order matters — the optimizer doesn’t always pick a new index

Creating a wider index doesn’t guarantee MySQL will use it for every related query — for a plain AND filter on both columns with no ORDER BY, the optimizer may keep using the narrower, already-familiar index if it judges the cost about the same:

EXPLAIN SELECT * FROM books WHERE genre = 'SciFi' AND published_year > 2010;
id  select_type  table  partitions  type  possible_keys                 key              key_len  ref    rows  filtered  Extra
1   SIMPLE       books  NULL        ref   idx_books_genre,idx_genre_year  idx_books_genre  203      const  834   33.33     Using where

possible_keys lists both indexes as candidates — key shows the optimizer chose the older, narrower idx_books_genre anyway for this particular query shape. Creating an index makes it available; the cost-based optimizer still decides, per query, whether it’s actually the cheapest option.

Practice

1. Confirm the composite index is unused when the query doesn't filter on its leading column. 
EXPLAIN SELECT * FROM books WHERE published_year = 2015;
id  select_type  table  partitions  type  possible_keys  key   key_len  ref   rows  filtered  Extra
1   SIMPLE       books  NULL        ALL   NULL           NULL  NULL     NULL  4980  10.00     Using where

idx_genre_year’s leading column is genre, not published_year — a query with no genre condition can’t use it at all (it isn’t even listed in possible_keys), and falls back to a full table scan just like the very first lesson’s ungindexed query.

2. Force the old single-column index on the sorted query and measure the difference directly. 
EXPLAIN ANALYZE SELECT * FROM books FORCE INDEX (idx_books_genre) WHERE genre = 'SciFi' ORDER BY published_year LIMIT 10;
-> Limit: 10 row(s)  (cost=99.2 rows=10) (actual time=1.02..1.02 rows=10 loops=1)
    -> Sort: books.published_year, limit input to 10 row(s) per chunk  (cost=99.2 rows=834) (actual time=1..1 rows=10 loops=1)
        -> Index lookup on books using idx_books_genre (genre='SciFi')  (cost=99.2 rows=834) (actual time=0.146..0.904 rows=834 loops=1)

FORCE INDEX overrides the optimizer’s own choice — forcing the narrower index reliably reproduces the Sort step on demand. The exact millisecond figures move a little between runs (1.02ms here versus 2.87ms earlier) since this is a shared, lightly-loaded container, not a dedicated benchmark rig — but the shape of the plan is what matters and is fully reproducible: with the composite index, no Sort step ever appears at all.

3. Check how many rows the sort actually had to handle before the fix, using the real SciFi count from lesson 1. 
SELECT COUNT(*) FROM books WHERE genre = 'SciFi';
COUNT(*)
834

The filesort in the buggy version had to sort all 834 matching rows to correctly return the first 10 by published_year — LIMIT 10 doesn’t help until after the full result set for the WHERE clause is sorted, which is exactly the cost the composite index eliminates by keeping those 834 rows pre-sorted in the first place.

Next: joins — how join order and type change the execution plan, measured.

Frequently Asked Questions

What does Using filesort actually mean in EXPLAIN's Extra column?
It means MySQL had to sort the result rows itself, in memory or on disk, after fetching them — the storage engine couldn't hand back rows already in the requested order. It doesn't necessarily mean a slow disk sort (small result sets sort in memory quickly), but it's real extra work that a well-chosen index can eliminate entirely.
Why did adding an index on (genre, published_year) remove the filesort?
A composite index stores its rows sorted by its columns in order — first by genre, then by published_year within each genre. Filtering to genre = 'SciFi' and asking for published_year order means the matching rows are already sitting in the index in exactly the order requested, so MySQL can read them off directly with no separate sort step needed.
Does column order in a composite index matter?
Yes, significantly — an index on (genre, published_year) supports 'filter by genre, sorted by year' efficiently, but an index on (published_year, genre) would not, since it's sorted by year first across every genre, not year-within-genre. The leftmost columns of a composite index are what a query's WHERE/ORDER BY need to align with to get the index's full benefit.
Why did EXPLAIN list the composite index as a possible key without MySQL choosing it for a different query?
For the earlier two-condition filter (genre AND published_year, no ORDER BY), the optimizer judged the existing single-column genre index just as good and kept using it — possible_keys lists every index that could theoretically apply, but key shows what the cost-based optimizer actually decided was cheapest for that specific query. Creating an index is a suggestion to the optimizer, not a directive it always follows.
Is it worth adding a composite index for every combination of WHERE and ORDER BY columns used?
No — every index speeds up matching reads but slows down every INSERT/UPDATE/DELETE on that table, since the index has to be maintained too. Composite indexes are worth adding for genuinely common, performance-sensitive query patterns (as measured, via EXPLAIN ANALYZE) — not preemptively for every column combination a query might theoretically use.