Boost MySQL Pagination Speed: Practical Query Optimizations and Benchmarks

Background

When a table contains millions of rows, retrieving all rows at once is slow; pagination is required. The article uses a table order_history with 5,709,294 rows to compare different pagination techniques.

Test Environment

Table: order_history (37 columns, primary key id auto‑increment)

MySQL version 5.7.16

Typical query: SELECT COUNT(*) FROM orders_history; returns 5,709,294 rows.

Baseline three simple LIMIT queries took ~8.9 s, 8.3 s, 8.4 s.

Simple LIMIT Pagination

Standard pagination uses LIMIT [offset,] rows. Example:

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

Benchmarks for different record counts show query time grows with the number of rows, but remains around 3 s for up to 10 000 rows. Offsets larger than 10 000 increase time noticeably.

Effect of Offset Size

Testing offsets from 100 to 1 000 000 reveals that queries with offsets greater than 100 000 experience a sharp rise in execution time (e.g., 14 s for 1 000 000 offset).

Subquery Optimization

First locate the starting id with a subquery, then fetch the page using that id. Example:

SELECT * FROM orders_history
WHERE type=8 AND id >= (
    SELECT id FROM orders_history WHERE type=8 LIMIT 100000,1
) LIMIT 100;

Benchmarks show the subquery version reduces execution time by up to three times compared with the plain LIMIT approach.

ID‑Range Optimization

If id values are continuous, compute the range for the required page and query with BETWEEN or a simple id >= … condition. Example:

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

Measured times are 9‑15 ms, demonstrating orders‑of‑magnitude speedup.

Alternative forms using IN (SELECT order_id FROM trade_2 WHERE goods='pen') are also possible, though some MySQL versions reject LIMIT inside IN.

Temporary‑Table Hint

When id is not strictly sequential (e.g., gaps or historical tables), store the page’s id values in a temporary table and join against it. This can dramatically improve performance for tables with tens of millions of rows.

General Recommendations

Prefer querying the primary key first (e.g., SELECT id … LIMIT …) and then fetch the full rows.

Ensure the table has an auto‑incrementing integer primary key.

For very large tables, consider sharding and using a distributed unique‑id generator instead of the native id.

Use the “id‑range” technique whenever the page boundaries are known.