How do you implement pagination efficiently in MySQL?

Two main pagination approaches, with very different performance characteristics: OFFSET-based (traditional): SELECT * FROM posts ORDER BY id DESC LIMIT 10 OFFSET 990; — page 100 with 10 per page. MySQL must scan and discard 990 rows before returning 10. For page 10,000: 99,990 discarded rows — extremely slow on large tables even with indexes, because MySQL reads all skipped rows. Keyset/Cursor pagination (recommended for large datasets): instead of OFFSET, filter based on the last seen value: SELECT * FROM posts WHERE id < :last_seen_id ORDER BY id DESC LIMIT 10; — uses the index directly, O(log n) regardless of page. Client receives the last id in the response and sends it for the next page. Advantages: consistent performance at any depth; stable results (OFFSET can skip or duplicate rows if new records are inserted between pages). Disadvantages: cannot jump to arbitrary pages; requires a sortable unique column. Deferred join (optimization for OFFSET when unavoidable): SELECT t.* FROM posts t JOIN (SELECT id FROM posts ORDER BY id DESC LIMIT 10 OFFSET 990) tmp ON t.id = tmp.id; — the subquery uses a covering index scan to get 10 IDs, then joins to fetch full rows for only those 10 — much faster than scanning full rows for 1000 records.