Why OFFSET/LIMIT Pagination Is Inefficient and How to Use Cursor‑Based Pagination

As data volumes grow, many developers still rely on the classic SELECT * FROM table_name LIMIT 10 OFFSET 40 pattern for pagination, but this method becomes increasingly inefficient for large tables.

When the OFFSET value is high, the database must scan and discard a huge number of rows, leading to full‑table scans that consume excessive I/O and memory, especially when the dataset exceeds the server's RAM.

For example, fetching the 5‑millionth page of a 100‑million‑row table requires reading all preceding rows, which can turn a simple query into a multi‑second operation, as demonstrated by a PoC where an OFFSET/LIMIT query took 30 times longer than an optimized version.

The recommended alternative is cursor‑based pagination, which uses a condition on an indexed, unique column (usually an auto‑increment ID or timestamp). A typical query looks like SELECT * FROM table_name WHERE id > 10 LIMIT 20 , allowing the database to jump directly to the relevant range without scanning irrelevant rows.

Benchmarks show that replacing SELECT * FROM table_name LIMIT 10 OFFSET 8000001 (12.80 s) with SELECT * FROM table_name WHERE id > 8000000 LIMIT 10 reduces execution time to 0.01 s, highlighting the dramatic performance gain.

When a unique sequential key is unavailable, the article advises still using OFFSET/LIMIT but being aware of its limitations and, if possible, adding an auto‑increment primary key to improve pagination speed.