Why is OFFSET pagination slow on large tables, and what is keyset pagination?

Summary

LIMIT 20 OFFSET 100000 makes MySQL read and discard 100,000 rows before returning 20, so deep pages get linearly slower. Keyset (seek) pagination remembers the last row's sort key and asks for WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20, which uses the index directly at any depth.

Longer Answer

Keyset pagination is also stable when rows are inserted while users page (no duplicates or skipped rows). Its limitation is that you cannot jump to an arbitrary page number; it suits infinite scroll, APIs and exports. The sort must be on a unique combination (add the primary key as a tiebreaker) backed by an index.

If you need page numbers, a deferred join helps: select only primary keys with the offset (covered by an index), then join back for the full rows.

Code Example

-- next page after the last row the client saw
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ('2026-10-10 12:00:00', 98231)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Key Points

• OFFSET reads and discards rows.
• Keyset: WHERE on last-seen sort key.
• Needs a unique, indexed sort order.
• Deferred join if page numbers are required.