MySQL Pagination Query Optimization Techniques and Performance Testing

When a table contains millions of rows, retrieving all records at once becomes slow; pagination is required. This article demonstrates several MySQL pagination methods and measures their performance on a table order_history with 5,709,294 rows (MySQL 5.7.16).

Preparation

The test table order_history has fields id (unsigned int, indexed), type (tinyint), and other columns. Basic count query: select count(*) from orders_history; Result: 5,709,294 rows.

General LIMIT Pagination

The simplest method uses the LIMIT [offset,] rows clause. SELECT * FROM orders_history LIMIT 1000, 10; This returns rows with id between 1001 and 1010. Timing tests show that increasing the offset dramatically increases query time, especially beyond 100,000.

Sub‑query Optimization

First locate the starting id with a sub‑query, then fetch the page:

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

Compared with the plain LIMIT query, this reduces execution time by about three times.

ID‑range Optimization

If the id column is continuous, compute the id range for the desired page and query directly:

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

Typical execution time is under 15 ms.

Temporary‑table Approach

When ids are not continuous, store the page ids in a temporary table and use an IN clause:

SELECT * FROM orders_history
WHERE id IN (
    SELECT order_id FROM trade_2 WHERE goods='pen'
)
LIMIT 100;

Note: some MySQL versions do not allow LIMIT inside an IN sub‑query.

Recommendations

Prefer selecting only the indexed id column to locate the offset, then fetch the full rows.

Use ID‑range or sub‑query methods for large offsets to avoid full table scans.

Ensure the id column is auto‑incremented; for sharded tables use a distributed unique identifier instead.

The article also includes a call‑to‑action for readers to join a discussion group and access additional resources.