Problems with OFFSET and LIMIT and Alternative High‑Performance Pagination Strategies
This article explains why using OFFSET and LIMIT for pagination on large tables leads to costly full‑table scans and poor performance, and introduces keyset (cursor) pagination as a faster, index‑driven alternative while discussing its requirements and trade‑offs.
When dealing with large datasets, using OFFSET and LIMIT for pagination can cause severe performance problems because the database must scan and discard many rows, leading to full table scans and high I/O overhead.
For example, retrieving the 5,000,000th page of a table with 100 million rows requires scanning 5 million rows before returning the desired 20 rows, which is highly inefficient ( 10万行中的第5万行到第5万零20行 ).
Alternative approaches such as keyset (cursor) pagination avoid this by remembering the last seen primary key and using it in the WHERE clause, allowing the database to use indexes and fetch only the needed rows.
Keyset pagination can dramatically reduce query time (e.g., from 12.8 seconds to 0.01 seconds) but requires a unique, ordered column like an auto‑increment ID or timestamp.
If a table lacks a suitable primary key, the traditional OFFSET / LIMIT method may be the only option, though it may still cause slow queries; adding an auto‑increment key is recommended.
Further reading and resources are provided for deeper understanding of efficient pagination techniques.
Architect's Guide
Dedicated to sharing programmer-architect skills—Java backend, system, microservice, and distributed architectures—to help you become a senior architect.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.