Why OFFSET/LIMIT Pagination Fails and How to Use Fast Cursor Pagination

Database pagination using OFFSET and LIMIT works for small datasets but becomes inefficient as data grows, requiring full table scans and high memory usage.

When OFFSET is large (e.g., 50 million out of 100 million rows), the database must read and discard millions of rows before returning the requested page, leading to slow queries. Rows 50,000 to 50,020 out of 100,000 rows Examples show that a naïve OFFSET/LIMIT query can be dozens of times slower than an optimized approach.

Problems with OFFSET and LIMIT

Full‑table scans, excessive I/O, and increased latency are the main issues.

Cursor‑based pagination alternative

Store the last seen primary key (or another unique, indexed column) and use it as the starting point for the next query instead of OFFSET.

This approach lets the database jump directly to the relevant rows using the index, dramatically reducing execution time.

Example of pointer‑based pagination query versus the original OFFSET/LIMIT query.

The optimized query runs in 0.01 seconds compared with 12.80 seconds for the OFFSET version.

Cursor pagination requires a unique sequential field (e.g., integer ID or timestamp). If a table lacks such a key, consider adding an auto‑increment primary key.

For large‑scale data queries, see Rick James’s article for deeper guidance: http://mysql.rjweb.org/doc.php/lists .