Turn a Full Table Scan into a Covered Query in MySQL

Confirm the scan with EXPLAIN, design a composite covering index, and verify Extra shows Using index so the query never touches the row.

~30 mindatabasemysqlperformance

Task

Identify a filesort-plus-full-scan query, build a covering composite index, and prove the access path changes to index-only reads.

When to use this

A leaderboard query scans two million rows on every page view. The server buys CPU spikes each evening while the query only needs three columns.

Prerequisites

MySQL 8.0+ with a testable copy of the data
The slow SQL statement and its WHERE/ORDER BY shape
Permission to add indexes (online DDL)

Steps

1. Confirm the current access path.

EXPLAIN SELECT user_id, score, updated_at FROM leaderboard WHERE season = 2026 ORDER BY score DESC LIMIT 100;

Expected output: type=ALL with rows=2 million and Extra containing Using filesort: every page view reads the whole table and sorts it.

2. Design a covering composite index for the exact shape.

CREATE INDEX idx_season_score_cover
ON leaderboard (season, score DESC, user_id, updated_at);

Expected output: The index leads with the filter column, orders by the second, and carries the selected columns so reads are satisfied from the index alone.

3. Re-check the plan for index-only access.

EXPLAIN SELECT user_id, score, updated_at FROM leaderboard WHERE season = 2026 ORDER BY score DESC LIMIT 100;

Expected output: type=ref or range, rows=100, and Extra shows Using index: no table lookup, no filesort, and the limit reads exactly one hundred index entries.

4. Measure the wall-clock improvement.

SELECT BENCHMARK(10, (SELECT count(*) FROM leaderboard WHERE season = 2026)); -- or run the query with profiling

Expected output: The leaderboard query drops from hundreds of milliseconds of CPU to sub-millisecond index reads, visible in the digest stats.

5. Guard the access path against regressions.

-- EXPLAIN FORMAT=JSON in CI, assert access type and Extra
EXPLAIN FORMAT=JSON SELECT ...;

Expected output: A regression test comparing the JSON plan keeps future schema changes from silently returning to a full scan.

Troubleshooting

Problem

Extra still shows Using filesort

Solution

The ORDER BY direction or column order in the index does not match the query; align index column order with equality filter first, then sort columns.

Problem

Index exists but is not chosen

Solution

Optimizer estimates may prefer another path; check with FORCE INDEX to confirm the index is usable, then look at statistics freshness.

Problem

Write throughput degrades

Solution

Every index adds write amplification; drop redundant single-column indexes that the new composite covers, and monitor the change rate.

Limitations

Covering indexes trade storage and write cost for read speed; they are wrong for rarely-run queries.
DESC in index definitions matters from MySQL 8.0 only; older versions ignore it and can still filesort.

The query only wanted three columns

A leaderboard needs one hundred rows with three fields, yet the default access path reads two million complete rows and sorts them on every request. EXPLAIN states this plainly as type ALL with Using filesort. A composite index that leads with the filter, follows with the sort key, and carries the selected columns turns the same query into one hundred ordered index entries read without ever opening a row. The plan output proves it before anyone believes it.

EXPLAIN SELECT user_id, score, updated_at
FROM leaderboard
WHERE season = 2026
ORDER BY score DESC LIMIT 100;

-- Before:  type=ALL  rows=2,000,000  Extra: Using where; Using filesort
-- After:   type=ref  rows=100        Extra: Using index

CREATE INDEX idx_season_score_cover
ON leaderboard (season, score DESC, user_id, updated_at);

Order the index like the query thinks

Equality predicates come first, then the sort columns in the query direction, then the remaining selected columns purely for coverage. This ordering lets the optimizer seek into exactly one season and walk the index in already-sorted order, which is why the LIMIT reads one hundred entries instead of two million rows.

Pin the plan into CI

A plan that improved by accident can regress the same way: a new column in the SELECT silently breaks coverage, or a type change defeats the sort. Capturing EXPLAIN FORMAT=JSON into a regression test makes the access path part of the build, and the full scan cannot sneak back in through a routine refactor.