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

In this article, the author (a self‑described "architect who writes code and poetry") discusses the challenges of database pagination for backend developers and architects.

Traditional pagination using OFFSET and LIMIT works fine for small datasets, but when the table contains millions of rows the database must perform a full table scan for each page request, which is extremely slow because it reads and discards a huge number of rows before returning the desired subset.

A full table scan reads every row sequentially, causing massive disk I/O and memory consumption, and is the slowest type of query.

For example, with 100,000 rows and an OFFSET of 50,000, the database still reads all 50,000 preceding rows before it can return the 20 rows requested by LIMIT . A public DB‑Fiddle demonstration shows the naïve query taking more than 30 times longer than an optimized version.

The recommended solution is cursor‑based pagination: store the primary key (or another unique, ordered column) of the last row retrieved and query the next page with a condition such as WHERE id > :last_id ORDER BY id ASC LIMIT :pageSize . This lets the database use an index to jump directly to the starting point, avoiding the scan of irrelevant rows.

In a side‑by‑side comparison, the naïve query took 12.80 seconds while the cursor‑based version completed in 0.01 seconds. The approach requires a unique sequential field (e.g., an auto‑increment integer or timestamp); if such a field is unavailable, the author suggests still using OFFSET/LIMIT but warns of potential slow queries.

Finally, the article points readers to further reading (e.g., Rick James’s guide on MySQL pagination) and invites them to join a community for deeper discussion.