Optimizing MySQL Deep Pagination for Millions of Rows: Four Practical Approaches

The author introduces the common pagination pattern using LIMIT and shows that when the offset grows large, query time degrades sharply, citing an example where LIMIT 100000,10 takes 0.742 seconds while LIMIT 0,10 finishes in 0.006 seconds.

Execution analysis reveals two main reasons: (1) MySQL must scan offset + limit rows before discarding the first offset rows, and (2) each scanned row may trigger a costly “row‑lookup” (回表) from the secondary index to the primary index.

1. Subquery Optimization – By moving the filter condition to the primary key index, the number of row lookups is reduced. The article provides the following SQL:

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10;

This version runs in 0.038 seconds, as shown by the execution plan.

2. INNER JOIN (Delayed Join) Optimization – Replaces the subquery with an inner join that first fetches qualifying primary‑key IDs using the secondary index, then joins back to the table:

SELECT acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 10) AS acct2 on acct1.id= acct2.id;

This query also finishes in about 0.034 seconds.

3. Tag‑Record Method – Stores the last processed primary‑key value (or a “bookmark”) and queries the next page with WHERE id > last_id ORDER BY id LIMIT 10 . This avoids large offsets entirely, assuming a monotonic, indexed column.

select id,name,balance FROM account where id > 100000 order by id limit 10;

While effective, it requires a continuously increasing key.

4. BETWEEN…AND… Optimization – When the exact range is known, replace LIMIT with a range condition that can be satisfied by an index scan:

select id,name,balance FROM account where id between 100000 and 100010 order by id;

The article then presents a real‑world case with a 2‑million‑row account table, describing a naïve pagination implementation that suffers from deep‑pagination slowdown, and demonstrates how the tag‑record technique can be applied in a loop to fetch data efficiently.

Overall, the piece offers concrete SQL snippets, execution‑plan screenshots, and performance numbers to guide backend developers in handling MySQL deep pagination at scale.