Problems with OFFSET/LIMIT Pagination and Cursor‑Based Alternatives

1. Problems with OFFSET and LIMIT

OFFSET and LIMIT work fine for small data sets, but when the table grows beyond what can fit in memory, each pagination request forces the database to perform an expensive full‑table scan.

A full‑table scan reads every row sequentially, checking each column against the query conditions, which results in massive disk I/O and high latency.

Full‑table scan (also called sequential scan) is the slowest type of query because it reads every row from disk into memory.

For example, if you have 100 million users and request OFFSET 50 million with LIMIT 20, the database must read all 50 million rows before it can return the 20 rows you asked for.

10万行中的第5万行到第5万零20行

This inefficiency becomes more severe as the offset grows, leading to query times that can be dozens of times slower than optimized approaches.

2. Alternative Solutions

Instead of OFFSET/LIMIT, use a cursor‑based pagination strategy: store the last seen primary‑key (or timestamp) and query the next page using a condition like WHERE id > last_id LIMIT 20 . This lets the database use an index to jump directly to the starting point, avoiding scanning irrelevant rows.

Example comparison (illustrated in the original images): the traditional OFFSET/LIMIT query took 12.80 seconds, while the cursor‑based version completed in 0.01 seconds.

Cursor‑based pagination requires a unique, ordered column (e.g., an auto‑increment ID or a timestamp). If such a column is unavailable, you may still need to fall back to OFFSET/LIMIT, but it is recommended to add an indexed surrogate key for efficient paging.

In summary, consider the trade‑offs of each method and choose the one that best fits your data size and access patterns.

Promotional Note: The author also invites readers to join a ChatGPT community offering resources, tutorials, and exclusive benefits. Details about membership pricing, bonuses, and additional content are provided in the latter part of the source.