MySQL Pagination Optimization Techniques and Performance Testing

Preparation

To evaluate different pagination optimizations, a MySQL 5.7.16 table named order_history with 5,709,294 rows is used. The table has 37 columns, an auto‑increment primary key id, and a type column used for filtering.

General Pagination Query

The simplest pagination uses the LIMIT clause. The syntax is:

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

Key points:

The first parameter is the offset (starting from 0).

The second parameter is the maximum number of rows to return.

If only one parameter is given, it represents the row count.

A second parameter of -1 means return all rows from the offset to the end.

Offsets are zero‑based.

Example query retrieving 10 rows after offset 1000 where type = 8:

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

Performance test results (three runs) show execution times of about 3 seconds for this query, while the same query without an offset (simple count) takes around 8‑9 seconds.

Subquery Optimization

This method first finds the id at the desired offset and then fetches rows after that id. It works well when id is monotonically increasing.

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 id >= (SELECT id FROM orders_history WHERE type = 8 LIMIT 100000, 1) LIMIT 100;

Four statements were timed, showing that using SELECT id instead of SELECT * reduces execution time by roughly threefold (e.g., 3674 ms → 1315 ms).

ID‑Range Optimization

If the primary key values are continuous, the page range can be calculated and queried directly with BETWEEN:

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

Execution time drops to a few milliseconds (15 ms, 12 ms, 9 ms). An alternative form uses a simple id >= 1000001 condition.

IN Clause Optimization

When the set of target id s comes from another table, an IN subquery can be used:

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 subquery.

Temporary Table Optimization

For cases where id values are not perfectly sequential (e.g., historical tables with gaps), a temporary table can store the pagination id s and then be used in an IN query, dramatically improving performance on tens‑of‑millions‑row tables.

About Table IDs

It is recommended to always add an auto‑increment id column to tables. For very large tables (e.g., order databases), consider using a distributed unique‑ID generator instead of the native auto‑increment to avoid bottlenecks. Combining range queries to locate id s and then fetching the full rows can speed up pagination several times.

Overall, the tests demonstrate that simple LIMIT offset, rows becomes increasingly slow as the offset grows, while strategies that avoid scanning from the first row—such as selecting the id first, using BETWEEN, or leveraging temporary tables—provide substantial performance gains.