Boost MySQL Pagination Performance: Proven Techniques and Benchmarks

Preparation

The test uses a table order_history with 5,709,294 rows, an auto‑increment id primary key, and a type column. MySQL version is 5.7.16.

Basic Pagination with LIMIT

A simple LIMIT clause can retrieve a page of rows. The syntax is:

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

Key points:

The first parameter is the offset, starting at 0.

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

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

An offset of -1 means return all rows from the offset to the end.

Example retrieving rows 1001‑1010 where type=8:

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

When ordered by id the same query can be written as:

SELECT * FROM orders_history WHERE type=8 ORDER BY id LIMIT 10000,10;

Performance of Basic LIMIT

Testing different row counts shows that query time grows with the number of rows returned, ranging from ~3 s for a few rows to ~3.8 s for 10,000 rows.

Sub‑query Optimization

First fetch the offset id and then query the subsequent rows:

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;

Timing results show the second query (selecting only id) is about three times faster than selecting all columns.

ID‑Range Optimization

If id values are continuous, calculate the range for the desired page and use a BETWEEN clause:

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

This query executes in under 15 ms. An alternative is:

SELECT * FROM orders_history WHERE id>=1000001 LIMIT 100;

IN‑Clause Optimization

When joining with another table, an IN sub‑query can be used:

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

Note that some MySQL versions do not support LIMIT inside an IN clause.

Temporary‑Table Optimization

For tables where id is not continuous (e.g., historical tables with gaps), store the page IDs in a temporary table and query using IN. This can dramatically improve pagination speed on tables with tens of millions of rows.

Guidelines for Table IDs

It is recommended to add an auto‑increment id column to every table. For very large datasets that are sharded, use a distributed unique ID generator instead of the database’s auto‑increment ID.

Conclusion

Combining an initial SELECT id to locate the offset with a subsequent SELECT * on the retrieved IDs can speed up pagination by several times, especially on large tables.