Why OFFSET/LIMIT Pagination Fails at Scale: Cursor-Based Alternative 1000x Faster

1. The Problem with OFFSET and LIMIT

OFFSET and LIMIT pagination works for small datasets but becomes pathological when data exceeds server memory. To fulfill a request like OFFSET 50000000 LIMIT 20 on 100 million rows, the database must perform a full table scan: it reads and materializes all 50 million preceding rows into memory before discarding them and returning the 20 requested rows. This incurs massive disk I/O and memory pressure.

Full table scan (sequential scan) reads every row in the table, checking each against the query conditions. It is the slowest access method because of heavy disk I/O and the overhead of moving data from disk to memory.

Concrete example: retrieving rows 50,000–50,020 from a 100,000-row table still requires fetching the first 50,000 rows. 10万行中的第5万行到第5万零20行 Evidence: a db-fiddle benchmark (https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1?ref=hackernoon.com) shows the OFFSET query running at least 30× slower than the optimized version. A GitHub proof-of-concept (https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC?ref=hackernoon.com) further illustrates the linear degradation: the higher the OFFSET, the longer the query takes.

2. Cursor-Based (Keyset) Pagination Alternative

Instead of OFFSET, store the last-seen primary key (usually an auto-increment ID or timestamp) and the page size. Each subsequent request uses a query like:

SELECT * FROM table WHERE id > :last_id ORDER BY id LIMIT :page_size;

Because the database can seek directly to the indexed value, it avoids scanning irrelevant rows. The article compares two queries visually:

Returning the same result set, the OFFSET query took 12.80 seconds while the keyset query took 0.01 seconds — a 1,280× speedup.

Requirements: a unique, sequentially ordered column (e.g., auto-increment integer ID or timestamp). If the table lacks such a column (common in many-to-many junction tables), OFFSET/LIMIT remains the only option but carries inherent slow-query risk. The author recommends adding an auto-increment primary key to any table that requires pagination, even if solely for that purpose.

For deeper guidance on large-data querying, the article references Rick James's MySQL pagination article: http://mysql.rjweb.org/doc.php/lists