MySQL Pagination Query Optimization Techniques and Performance Testing

When a database table contains millions of rows, retrieving all records at once becomes slow, so pagination is essential; this article discusses several MySQL pagination strategies and their optimizations.

Preparation

The test uses an existing table order_history with 5,709,294 rows, 37 columns, an indexed auto‑increment id, and MySQL 5.7.16.

General Pagination

Typical pagination uses the LIMIT clause, where the first argument is the offset (starting from 0) and the second is the number of rows to return.

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

Example:

select * from orders_history where type=8 limit 1000,10;

This returns rows 1001‑1010 based on the primary‑key order.

Performance tests on three queries with different limits showed execution times around 3 seconds for 1,000‑row limits.

Limit Size Impact

Increasing the row count in the LIMIT clause raises query time: 1 row (~3 s), 10 rows (~3 s), 100 rows (~3.1 s), 1,000 rows (~3.4 s), 10,000 rows (~3.8 s).

Offset Impact

Testing various offsets with a fixed row count (100) revealed that larger offsets dramatically increase latency, especially beyond 100,000 rows (e.g., 10,000 offset ≈ 3 s, 100,000 offset ≈ 3.9 s, 1,000,000 offset ≈ 14 s).

Sub‑query Optimization

This method first selects the id at the desired offset, then retrieves rows using that id, which can be several times faster than scanning from the start.

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 tested showed the SELECT id version reduced time from ~3.7 s to ~1.3 s.

ID‑Range Optimization

If id values are continuous, calculate the range for the desired page and use BETWEEN or a simple id >= condition, achieving query times of 9‑15 ms for 100 rows.

select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;

select * from orders_history where id >= 1000001 limit 100;

Using IN with a sub‑query can also work, though some MySQL versions disallow LIMIT inside IN.

select * from orders_history where id in (select order_id from trade_2 where goods='pen') limit 100;

Temporary‑Table Optimization

When id continuity is broken (e.g., historical tables), store the pagination id list in a temporary table and query with IN, greatly improving speed for tens of millions of rows.

Notes on Table IDs

Typically tables have an auto‑increment id for easy querying; for very large datasets, a distributed unique‑ID generator is recommended instead of relying on the primary key alone.

Combining range or index lookups (first fetch id, then fetch full rows) can boost performance severalfold.

Promotional Section

The author also advertises a compiled PDF of 3,625 pages containing 96 sets of interview questions from major internet companies, encouraging readers to add a WeChat contact (remark "面试题") to receive the material and join related tech groups.

Various images and QR codes are included to facilitate the download and community joining.