Why OFFSET Pagination Slows Down MySQL and How Keyset Pagination Solves It

1. What is pagination

Pagination splits a large result set into smaller pages so that a client can request a limited number of rows at a time. It reduces the amount of data transferred per request, improves perceived responsiveness, and lowers the load on the database.

2. Benefits of pagination

Improved user experience : users see a manageable amount of data and can navigate quickly.

Reduced server and network load : each request returns only the rows needed for the current page.

Lower backend pressure : the database scans fewer rows, which reduces CPU, I/O and lock contention.

Easier data management : administrators can filter, sort, and operate on a bounded range of records.

3. Implementation methods

3.1 OFFSET pagination (traditional)

OFFSET pagination relies on the LIMIT and OFFSET clauses to specify how many rows to return and how many rows to skip.

SELECT *
FROM table_name
ORDER BY id
LIMIT 10 OFFSET 20;

Advantages

Simple to implement; supported by virtually every SQL database.

Low development and maintenance cost.

Allows random page jumps (e.g., directly request page 100).

Works well for small‑to‑medium data sets where performance is not critical.

Disadvantages

Performance degradation : MySQL (and most engines) must read and discard all rows up to the offset, causing I/O and CPU usage to grow linearly with the offset.

Data‑consistency issues : inserts or deletes between page requests can produce duplicate or missing rows.

Unsuitable for real‑time updates : frequent data changes break pagination accuracy.

High resource consumption : each query performs a full sort and scan, which is especially costly for deep pagination.

3.2 Keyset pagination (cursor)

Keyset pagination (also called cursor pagination) uses the last row’s unique identifier—typically a primary key or a monotonic timestamp—as the starting point for the next page, eliminating the need for an offset.

SELECT *
FROM table_name
WHERE id > 100
ORDER BY id
LIMIT 10;

Advantages

Superior performance: the engine can jump directly to the first qualifying row using an index.

Stable result set: inserts or deletes after the cursor do not affect the rows already returned.

Disadvantages

Jump‑to‑page is difficult because the cursor of the target page is unknown without traversing previous pages.

Requires a globally unique, ordered column (primary key, UUID, or timestamp).

Typical scenarios : large, frequently updated streams such as social‑media feeds, message lists, or AI‑generated recommendation feeds.

4. Why OFFSET is slow

4.1 Execution process

Full scan or index scan : MySQL reads rows in the order defined by ORDER BY, potentially scanning the whole table.

Skipping rows : the engine iterates through the result set and discards each row until the offset is reached.

Reading target rows : after the offset, the requested LIMIT rows are returned.

4.2 Root causes

Scanning and discarding waste resources : disk I/O, memory and CPU are consumed for rows that are never returned.

Inability to jump directly using indexes : MySQL cannot calculate the physical position of the offset without scanning.

Sorting overhead : if the ORDER BY column lacks an index, MySQL must sort the entire data set, often using a temporary table.

Deep pagination amplifies the problem : larger offsets increase the amount of work linearly.

4.3 Typical problem scenarios

Millions of rows with deep offsets (e.g., OFFSET 1000000) cause severe slowdown.

Concurrent inserts/deletes lead to duplicate or missing rows.

Sorting on non‑indexed columns forces a full‑table sort.

4.4 Mitigation techniques

Use keyset pagination

SELECT *
FROM t1
WHERE id > #{last_id}
ORDER BY id ASC
LIMIT 20;

Directly locates the start row, eliminating offset‑related work.

Limit deep‑page range : expose only a sliding window of pages (e.g., the most recent 100 pages) to avoid very large offsets.

Subquery optimization : first fetch the primary‑key range, then join to retrieve full rows.

SELECT *
FROM t1
JOIN (
  SELECT id
  FROM t1
  ORDER BY id ASC
  LIMIT 1000000, 20
) x USING (id);

This reduces sorting and back‑table look‑ups.

Proper index design : create covering indexes on the columns used for filtering and ordering (e.g., CREATE INDEX idx_table_name_id ON table_name(id);). A covering index allows MySQL to satisfy the query using only the index without touching the table.

Alternative techniques : reverse pagination (use ORDER BY id DESC and WHERE id < last_id), delayed joins (fetch IDs first, then retrieve payload), partitioned tables (so each partition contains a smaller range), or business‑logic sharding to keep page sizes small.

5. Conclusion

Pagination is essential for user‑facing applications, but naïve OFFSET pagination becomes a hidden performance killer on large data sets. By adopting keyset pagination, designing appropriate indexes, and applying query‑level tricks such as subquery range fetches or limiting deep‑page ranges, developers can achieve scalable, responsive paging that balances user experience with system efficiency.