Boost MySQL Pagination: Practical Optimizations and Performance Benchmarks

When a table contains millions of rows, retrieving all records at once becomes slow, especially as data grows; pagination is required. The article presents multiple MySQL pagination strategies and measures their performance.

Preparation

The test uses a table order_history with 5,709,294 rows, primary key id (unsigned int) and a type column, running on MySQL 5.7.16. select count(*) from orders_history; The count query returns 5,709,294 rows. Three baseline full‑table queries took roughly 8,900 ms, 8,300 ms and 8,400 ms.

General LIMIT pagination

Standard pagination uses the LIMIT clause:

SELECT * FROM orders_history WHERE type=8 LIMIT 1000,10;

Here offset starts at 0; the second argument specifies the number of rows to return. The three runs for a 10‑row page took about 3,040 ms, 3,063 ms and 3,018 ms.

Varying the number of rows returned shows modest increases: 1 row (~3,000 ms), 10 rows (~3,050 ms), 100 rows (~3,150 ms), 1,000 rows (~3,400 ms), 10,000 rows (~3,750 ms). The query time grows as the result set size grows.

Testing different offsets reveals a sharp slowdown for large offsets: offset 100 (≈25 ms), 1,000 (≈77 ms), 10,000 (≈3,100 ms), 100,000 (≈3,800 ms), 1,000,000 (≈14,500 ms). The simple LIMIT scans from the first row, so larger offsets dramatically increase latency.

Subquery optimization

This method first finds the starting id with a subquery, then fetches the page using that id:

select * from orders_history where type=8 limit 100000,1;
select id from orders_history where type=8 limit 100000,1;
select * from orders_history where type=8 and id >= (select id from orders_history where type=8 limit 100000,1) limit 100;

Four statements were timed: 3,674 ms, 1,315 ms, 1,327 ms, and 3,710 ms. Selecting only the id reduces execution time roughly threefold.

ID‑range optimization

If id values are continuous, compute the range for the required page and use BETWEEN:

SELECT * FROM orders_history WHERE type=2 AND id BETWEEN 1000000 AND 1000100 LIMIT 100;

Execution times were 15 ms, 12 ms and 9 ms, orders of magnitude faster than the previous methods.

Alternative forms include id >= 1000001 LIMIT 100 or using IN with a subquery for multi‑table joins.

Temporary‑table approach

When id values are not strictly continuous (e.g., historical tables with gaps), store the page’s id s in a temporary table and query with IN. This can greatly improve performance on tables with tens of millions of rows.

Id field recommendations

Always define an auto‑increment primary key for each table. In sharded environments, prefer a distributed unique‑id generator instead of relying on the auto‑increment id. A common pattern is to first select the needed id s, then retrieve full rows using those id s, which can speed up queries several times.

The author notes that the presented methods are based on personal testing and invites readers to point out any errors.