MySQL Deep Pagination: Why OFFSET 99980 Takes 4.68s and How to Cut It to 6ms

LIMIT in the Server Layer

The query SELECT * FROM t_order ORDER BY id LIMIT 99980, 20 took 4.68 seconds on page 5000 while page 1 took 18 ms. The SQL differs only in offset. LIMIT M, N means skip M rows and return the next N rows. MySQL implements this by actually reading the first M rows and discarding them. InnoDB's leaf nodes are doubly linked, but it cannot start from the M-th node; it must count from the beginning. LIMIT 20 OFFSET 99980 and LIMIT 99980, 20 parse to the same execution plan.

Two Execution Paths

When the sort column has a usable index with matching order, the plan shows type: index. MySQL reads the index sequentially. SELECT * forces a primary-key lookup (clustered index) for every row — 100,000 random I/Os. The slowness is not the sort but these lookups.

mysql> EXPLAIN SELECT * FROM t_order ORDER BY id LIMIT 99980, 20
G
id: 1
select_type: SIMPLE
table: t_order
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 100020
Extra: NULL

If the sort column lacks an index or the index order mismatches ORDER BY, the plan becomes type: ALL with Using filesort. This path reads all qualifying rows into the sort buffer, sorts them, then discards the first 99,980 rows. Offset size does not affect scan volume; the whole table is read plus a sort cost. Adding a create_time index often fails because the optimizer still chooses filesort or a full scan.

The difference determines that offset optimization only helps the first path; the second requires fixing the sort first.

The rows Number in EXPLAIN

rows

is an optimizer estimate, not actual rows read. Statistics drift can make it an order of magnitude too low. Use EXPLAIN ANALYZE (MySQL 8.0.18+) for actual rows and timing per operator. Without 8.0.18, enable session Handler counters:

FLUSH STATUS;
SELECT * FROM t_order ORDER BY id LIMIT 99980, 20;
SHOW SESSION STATUS LIKE 'Handler_read%';

Handler_read_key

= index seeks, Handler_read_next = sequential index reads, Handler_read_rnd_next = full-table scans. After the deep pagination query, Handler_read_next approaches 100,000 and Handler_read_rnd_next near 0. For a full scan the values swap. These counters are unaffected by statistics and are faster than re-reading EXPLAIN estimates when debugging "why did my index not help?".

Delayed Join (Deferred Join)

Since the cost is 100,000 lookups, eliminate them. First find the 20 primary keys on the index, then join back to fetch full rows.

SELECT o.*
FROM t_order o
INNER JOIN (
    SELECT id FROM t_order ORDER BY id LIMIT 99980, 20
) AS page ON o.id = page.id
ORDER BY o.id;

The inner query selects only id. InnoDB secondary index leaves store (index columns + primary key); the clustered index stores the full row. SELECT id is satisfied by any index without a lookup — a covering index. Scanning 100,000 index entries is sequential I/O, not 100,000 random reads.

The outer ORDER BY o.id is mandatory. JOIN does not guarantee output order; the derived table's order would be lost, causing duplicate or missing rows on pagination.

Keep the LIMIT inside the derived table. MySQL will not merge a derived table that contains LIMIT. Moving LIMIT to the outer query allows merging, flattening the conditions, losing the covering index, and reverting to the original plan. Always re-check EXPLAIN after rewriting.

For non-primary-key sorts (e.g., ORDER BY create_time DESC), delayed join works if a covering index exists. With idx_create_time(create_time), the leaf is (create_time, id) because the primary key is implicitly appended. SELECT id is still covered. A single-column index suffices; (create_time, id) is equivalent for this query but may help other queries. If no index exists, the inner query itself does filesort and delayed join cannot help — create the composite index first.

Cursor-Based Pagination (Keyset Pagination)

Delayed join reduces latency from 4.68 s to ~200 ms but still scans 100,000 index entries. Page 50,000 would scan 1,000,000 entries — linear growth. Cursor pagination avoids the offset entirely by remembering the last row's key.

SELECT * FROM t_order WHERE id > 9980 ORDER BY id LIMIT 20;

Here 9980 is the last id from the previous page. The B+ tree seeks directly to the next record; scan volume is fixed at 20 rows regardless of page depth. The plan changes from index to range with rows: 20.

Limitations: the user cannot jump to an arbitrary page (no page-number input), only "next/previous". Non-unique sort keys cause missing rows. Example: eight orders share the same create_time; a boundary cutting through them with WHERE create_time < '2026-10-01 10:00:00' skips the remainder. Fix by adding the primary key as a tie-breaker using row-value comparison:

SELECT * FROM t_order
WHERE (create_time, id) < ('2026-10-01 10:00:00', 8821)
ORDER BY create_time DESC, id DESC
LIMIT 20;

Row-value comparison uses an index only if a composite index (create_time, id) exists; a single-column index is insufficient. The cursor payload changes from a single value to a pair, requiring API changes.

Another failure case: the sort column is updated. Paginating by updated_at DESC, a row updated during pagination jumps to the front and appears again later. Cursor pagination assumes a static dataset; write-heavy tables need a different approach.

Total Count, Page Jumps, and Product Trade-offs

Deep pagination often coexists with a total-count query for page-number UI. SELECT COUNT(*) FROM t_order WHERE status = 3 InnoDB must scan the smallest index (MVCC prevents a stored exact count). On a million-row table this takes 200–300 ms, adding to the pagination latency. SQL_CALC_FOUND_ROWS was a one-round-trip trick but is deprecated since 8.0.17 because it disables optimizations and runs as slow as a full scan without LIMIT. The replacement is two separate statements:

SELECT id, order_no, amount, status FROM t_order
WHERE status = 3 ORDER BY id LIMIT 20;

SELECT COUNT(*) FROM t_order WHERE status = 3;

Product-level mitigations: cache the total in Redis with a short TTL; show an estimated total from

SHOW TABLE STATUS

Rows

(near-zero cost); move "export all" to an async batch job. For the customer-service history-order scenario (few pages then export), caching the count and offloading export is cheaper than rewriting the pagination API.

Benchmark: Rewriting a Slow Query End-to-End

Test table: 1.28M rows, id auto-increment PK, idx_create_time single-column index, idx_status. Query Cache removed in 8.0. Each variant run three times; median taken on same machine/client to avoid network variance.

LIMIT 99980, 20 : Scanned rows 100020, Lookups 100020, Latency 4.68 s

Delayed Join : Scanned rows 100020, Lookups 20, Latency 0.21 s

Cursor Pagination : Scanned rows 20, Lookups 20, Latency 0.006 s

Delayed join eliminates lookups but not the index scan, so it suits "need random page jumps, data under tens of millions" — 4 s to 200 ms is acceptable. Scan grows linearly with page depth (page 500 scans 10k, page 50k scans 1M), but the slope is far gentler than the original per-row lookup. Cursor pagination removes the scan entirely; cost is giving up random page access.

Critical verification: after rewrite, confirm EXPLAIN shows the expected key. A delayed join may report lower latency yet use idx_status with a hidden filesort because the data volume hasn't yet hit the pain threshold. When the table doubles, the problem resurfaces. Always check key and Extra columns, not just response time.