Optimizing MySQL Pagination for Large Datasets: Techniques and Examples

The author, a senior architect, investigates the performance degradation of MySQL pagination when using LIMIT offset, count on a table with 500,000 rows. Initial tests show query times increasing from 0.05 s (offset 0) to 0.21 s (offset 499,000) because the LIMIT clause forces a full‑table scan.

Using EXPLAIN, the scan is confirmed as a full scan (type ALL) with millions of rows examined, highlighting the need to reduce the scanned range.

Optimization Strategy 1 – Ordered Unique Index : By ensuring the primary key id is ordered, the query can be rewritten to locate the start row first and then fetch the next ten rows, e.g.:

SELECT * FROM edu_test WHERE id > 499000 ORDER BY id ASC LIMIT 10;

This reduces the scanned rows to about 1,000 and cuts execution time to ~0.09 s.

Optimization Strategy 2 – Sub‑query : Use a sub‑query to find the starting id and then select rows based on that value:

SELECT * FROM edu_test WHERE id >= (
    SELECT id FROM edu_test ORDER BY id LIMIT 499000, 1
) LIMIT 10;

The plan shows a range scan on the primary key, improving performance to ~0.14 s.

Optimization Strategy 3 – Join Query : Combine a derived table that fetches the target ids with a join to the main table:

SELECT * FROM edu_test s,
       (SELECT id FROM edu_test ORDER BY id LIMIT 499000, 10) t
WHERE s.id = t.id;

This approach also limits the scanned rows and achieves execution time around 0.10 s.

In real business scenarios, designing primary keys that are unique and ordered (or using patterned IDs) helps avoid hotspot issues and enables these optimizations. The author also suggests using WHERE id LIKE '10289%' to further narrow the scan range when appropriate.

Overall, the article emphasizes that a well‑designed primary key and careful query rewriting can dramatically reduce pagination latency on large MySQL tables.