Pagination Optimization
LIMIT/OFFSET becomes slow on large tables because MySQL still scans all rows up to the offset. Use keyset pagination (cursor-based) for deep pages.
MySQL
Advanced
5 min read
Optimizing Pagination
Example
-- Slow for large offsets
SELECT id, name FROM users ORDER BY id LIMIT 20 OFFSET 100000;
-- Keyset pagination (fast)
SELECT id, name FROM users
WHERE id > 100020 -- last seen id
ORDER BY id
LIMIT 20; Pro Tip
Keyset pagination is O(1) regardless of page depth; OFFSET is O(n).