✏️ Explanatory Question
Level: Very Hard — Every large paginated system hits this wall; keyset pagination is the senior-level answer.
LIMIT 0, 20) is instant. But a user jumping to page 50,000 (LIMIT 1000000, 20) makes the API take 30 seconds and spike CPU. The index is there and used. Why is a "deep" page so slow?
-- posts table: 10 million rows, indexed on (created_at, id)
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 1000000, 20; -- page 50,001The cause: LIMIT 1000000, 20 does not skip straight to row 1,000,000. MySQL must read, sort, and discard the first 1,000,000 rows, then return the next 20. The deeper the page, the more rows are scanned and thrown away. The work grows linearly with the offset.
$$ \text{Rows processed} = OFFSET + LIMIT = 1{,}000{,}000 + 20 $$
| Page | OFFSET rows scanned | Keyset rows scanned |
|---|---|---|
| Page 1 | 20 | 20 |
| Page 1,000 | 20,020 | 20 |
| Page 50,000 | 1,000,020 | 20 |
Keyset pagination reads a constant ~20 rows regardless of depth — it's O(1) vs OFFSET's O(n).
-- SLOW: OFFSET scans and discards 1M rows
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 1000000, 20;
-- FAST: remember the LAST row of the previous page, then seek past it
-- Suppose the last row shown was: created_at='2026-03-15 10:00:00', id=8423122
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-03-15 10:00:00', 8423122) -- seek condition
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Uses the index to jump directly; reads only 20 rowsWHERE uses the indexed sort columns, MySQL seeks directly to the position — no scanning of skipped rows. The tuple comparison (created_at, id) < (...) handles ties correctly.
-- Fetch only IDs via the covering index (cheap), then join for full rows
SELECT p.id, p.title, p.created_at
FROM posts p
JOIN (
SELECT id FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 1000000, 20
) AS keys ON p.id = keys.id
ORDER BY p.created_at DESC, p.id DESC;
-- The inner query scans a narrow index; the outer fetches just 20 full rows