Why Does LIMIT Pagination Slow Down? Deep Dive and Optimizations

During a rainy night, a developer receives an alert about a slow query after adding a new field and writing a data‑refresh interface that uses pagination. The table contains a large amount of data, so reading everything into memory is impossible; pagination is used instead. However, as the offset grows, the query becomes increasingly slow.

Reproducing the Slow Query

A Product table is created with many columns and several indexes, including idx_timeCreated on timeCreated. A shallow pagination query:

select * from Product where timeCreated > "2020-09-12 13:34:20" limit 0,10

executes in 1 ms. The same query with a deep offset:

select * from Product where timeCreated > "2020-09-12 13:34:20" limit 10000000,10

takes 27 499 ms, reproducing the problem.

Why Deep Pagination Is Slow

MySQL uses B+‑tree indexes. A clustered index (the primary key) stores full rows at leaf nodes, while a non‑clustered (secondary) index stores only the indexed columns and the primary key. For the deep pagination query, MySQL first uses the secondary index idx_timeCreated to locate matching rows, then performs a “back‑table” (回表) lookup for each row to retrieve the full record. With a large offset, MySQL still scans all preceding rows, causing millions of unnecessary back‑table operations and heavy disk I/O.

Optimization Strategies

All solutions aim to reduce useless back‑table lookups.

Subquery method : First fetch the primary key of the row at the desired offset, then query the next ten rows using that key.

select * FROM Product where id >= (
  select p.id from Product p where p.timeCreated > "2020-09-12 13:34:20" limit 10000000,1
) limit 10;

Execution time drops to ~2.5 s.

INNER JOIN method : Similar to the subquery, but joins the result set of the indexed subquery with the main table.

select * from Product p1 inner join (
  select p.id from Product p where p.timeCreated > "2020-09-12 13:34:20" limit 10000000,10
) as p2 on p1.id = p2.id;

Execution time is comparable (~2.5 s).

Anchor‑record (tag) method : Use the last seen primary key as a cursor for the next page, eliminating large offsets.

select * from Product p where p.timeCreated > "2020-09-12 13:34:20" and id > 10000000 limit 10;

Runs in 1 ms, but requires monotonically increasing IDs and cannot support arbitrary page jumps.

Range (BETWEEN) method : Similar to the anchor method but uses a BETWEEN clause; beware of inclusive bounds that may return an extra row.

When to Use External Search Engines

If data volume grows further, moving the query to a dedicated search engine like Elasticsearch may be considered, though ES also suffers from deep pagination issues.

Final Example

The author finally solves the original problem with an INNER JOIN on InventorySku:

select * from InventorySku isk inner join (
  select id from InventorySku where inventoryId = 6058 limit 109500,500
) as d on isk.id = d.id;

This confirms that reducing back‑table operations effectively eliminates the slow query.