Boost MySQL Pagination Speed: Proven Techniques and Benchmarks

Preparation

To test the optimizations below, we use an existing table named order_history with the following characteristics:

Table name: order_history

Description: order history for a business

Main fields: unsigned int id, tinyint(4) type 37 columns, max varchar(500), id is indexed and auto‑incrementing

Row count: 5,709,294

MySQL version: 5.7.16

Counting rows: select count(*) from orders_history; Result: 5,709,294 rows.

General Pagination Query

The simplest pagination uses the LIMIT clause. Syntax:

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

Key points:

The first argument is the offset (starting at 0).

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

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

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

Offsets are zero‑based, not one‑based.

Example:

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

This returns 10 rows starting from offset 1000 (i.e., rows with id 1001‑1010).

Performance (three runs): 3040 ms, 3063 ms, 3018 ms.

Sub‑Query Optimization

This method first finds the id at the desired offset, then retrieves rows based on 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 type=8 and id>=(select id from orders_history where type=8 limit 100000,1) limit 100;

select * from orders_history where type=8 limit 100000,100;

Execution times:

Statement 1: 3674 ms

Statement 2: 1315 ms

Statement 3: 1327 ms

Statement 4: 3710 ms

Observations:

Replacing SELECT * with SELECT id speeds up the query roughly threefold.

The third statement adds only a few dozen milliseconds over the second.

The fourth statement benefits from the faster SELECT id in the sub‑query.

ID‑Range (Between) Optimization

If id values are continuous, calculate the range for the desired page and query directly:

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

Execution times: 15 ms, 12 ms, 9 ms.

Alternative forms:

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

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.

Temporary Table Optimization

When id is not strictly sequential (e.g., historical tables with gaps), store the pagination id values in a temporary table and query using IN. This can dramatically improve performance on tables with tens of millions of rows.

About Table IDs

It is best practice to add an auto‑incrementing id column to every table for easy querying. For very large tables (e.g., order databases), consider sharding and using a distributed unique‑ID generator instead of the native auto‑increment.

Typical workflow: first select the id (or a range of id s), then fetch the full rows using the primary key index. This two‑step approach can accelerate queries by several times.

Feel free to point out any mistakes or omissions.