Why OFFSET/LIMIT Slows Down Large Datasets and How to Use Cursor Pagination

The article explains why OFFSET causes performance issues on large datasets and proposes a high‑performance pagination method.

1. Problems with OFFSET and LIMIT

OFFSET works fine for small tables, but when the table grows beyond memory capacity, each page request forces a full table scan. A full table scan reads every row sequentially, causing massive disk I/O and memory usage. For example, with 100 million rows and OFFSET = 50 million, the database must read 50 million rows just to discard them before returning the 20 rows requested. 10万行中的第5万行到第5万零20行 Benchmarks show the naive query can be 30× slower than an optimized version.

2. Alternative: Cursor‑Based Pagination

Instead of OFFSET, store the last seen primary‑key (or a unique, indexed column) and request the next page with a condition like WHERE id > :last_id LIMIT :pageSize. The database can use the index to jump directly to the start row, eliminating the scan.

The optimized query returns the same result in 0.01 s compared with 12.80 s for the OFFSET version.

Cursor pagination requires a unique sequential column (ID, timestamp, etc.). If such a column is unavailable, OFFSET/LIMIT may be the only option, but be aware of its potential slowness.

For deeper guidance see Rick James’s article on MySQL list pagination.