Symptom
A query that looks simple runs for 8–30 seconds:
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
The table has 2 million rows and an index on status. EXPLAIN still shows Using filesort. Adding an index on created_at doesn't help either.
Why ORDER BY + LIMIT is slow
MySQL has to sort before it can cut
When there's no index that covers both WHERE and ORDER BY, MySQL must:
- Scan all rows matching
WHERE status = 'pending'— potentially hundreds of thousands of rows - Sort that result set by
created_at DESC— filesort, in memory or on disk - Return the first 20 rows
LIMIT 20 doesn't help if MySQL can't stop early — it still has to finish sorting the entire result set first.
What filesort actually means
Using filesort in EXPLAIN does not mean sorting to a file. It's MySQL's term for any sort that can't use an index — it may run entirely within sort_buffer_size (RAM), or spill to disk if the dataset is too large.
EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
id | type | key | rows | Extra
1 | ref | idx_st | 450000 | Using index condition; Using filesort
rows: 450000 — MySQL estimates it needs to process 450k rows before sorting and applying LIMIT.
Common causes
1. WHERE and ORDER BY use different columns
-- WHERE on status, ORDER BY on created_at
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;
An index on (status) helps with filtering, but the filtered result has no ordering on created_at — filesort required.
An index on (created_at) helps with sorting, but MySQL may skip it if the optimizer decides that scanning the created_at range and then filtering status costs more than a filesort.
2. Large offset in pagination
-- Page 5000, 20 rows per page
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
MySQL reads 100,020 rows, discards the first 100,000, and keeps 20. The larger the offset, the slower the query — even when an index is used.
3. ORDER BY on an expression or function
-- Sorting by an expression → index cannot be used
SELECT * FROM orders ORDER BY DATE(created_at) DESC LIMIT 20;
SELECT * FROM products ORDER BY price * 0.9 DESC LIMIT 20;
An index on created_at doesn't apply to DATE(created_at).
4. SELECT * defeats covering indexes
With a covering index, MySQL can resolve the entire query from the index without touching the table rows. SELECT * breaks this — MySQL is forced to fetch the full row for every match.
How to diagnose
Reading EXPLAIN correctly
EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20\G
What to look for:
| Field | Bad signal | Good signal |
|---|---|---|
type |
ALL, index |
ref, range, eq_ref |
rows |
Large number (> 10,000) | Close to LIMIT value |
Extra |
Using filesort, Using temporary |
Using index |
key |
NULL |
A specific index name |
EXPLAIN ANALYZE (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
Returns actual row counts and real timing — more useful than plain EXPLAIN because it shows whether the optimizer's estimates match reality.
How to fix it
1. Composite index covering both WHERE and ORDER BY
Create an index on (status, created_at):
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
MySQL can now:
- Use
statusto enter the right partition in the index - Use the pre-sorted
created_atorder in the index to return 20 rows without a filesort
EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
-- Extra: Using index condition ← no more filesort
-- rows: 20 ← reads exactly 20 rows
Column order in the composite index matters: equality WHERE columns come first, ORDER BY columns come after.
-- Correct: equality first, sort column after
INDEX (status, created_at)
-- Wrong order: can't use it for sorting
INDEX (created_at, status)
2. Cursor-based pagination instead of OFFSET
Instead of:
-- Slow at large offsets
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
Use a cursor (the last seen value):
-- First page
SELECT id, created_at, status FROM orders
ORDER BY created_at DESC
LIMIT 20;
-- Next page: pass in the created_at of the last row returned
SELECT id, created_at, status FROM orders
WHERE created_at < '2026-05-10 14:30:00' -- cursor from previous page
ORDER BY created_at DESC
LIMIT 20;
MySQL seeks directly to the cursor position in the created_at index and reads exactly 20 rows — no skipping.
Note: cursor pagination only works when users don't need to jump to an arbitrary page. For admin dashboards with "go to page 500" functionality, a different approach is needed.
3. Deferred join for large OFFSET
When OFFSET is unavoidable, reduce the data read by fetching only id first, then joining back:
-- Slow: reads entire rows then discards most of them
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
-- Faster: scan index-only for ids, then fetch only 20 rows
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000
) sub ON o.id = sub.id;
The subquery only reads the index (covering), never touches row data. The outer JOIN fetches only 20 specific rows. For wide tables with many columns this is significantly faster.
4. Covering index
If the query only needs specific columns, create an index that includes all of them:
-- Query
SELECT id, status, created_at, total FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
-- Covering index includes every column the query needs
ALTER TABLE orders ADD INDEX idx_covering (status, created_at, id, total);
MySQL resolves the entire query from the index tree without reading row data (Extra: Using index). Especially fast for wide tables or large row sizes.
When the optimizer picks the wrong index
Sometimes MySQL has a good index but doesn't use it — cost estimates can be off.
-- Force a specific index to test
SELECT * FROM orders USE INDEX (idx_status_created)
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
If the query is faster with the forced index, statistics may be stale:
ANALYZE TABLE orders;
ANALYZE TABLE refreshes index statistics, giving the optimizer more accurate estimates — especially after heavy inserts or deletes.
Optimization checklist for ORDER BY + LIMIT
- Does EXPLAIN still show
Using filesort? → need a composite index - Is
rowsin EXPLAIN close to LIMIT or far away? → close is good - Is OFFSET large? → consider cursor-based pagination
- Is
SELECT *necessary? → fetch only needed columns, create a covering index - Sorting by a function or expression? → remove the function or use a generated column
- Has
ANALYZE TABLEbeen run recently? → index stats may be stale after heavy writes