Boost MySQL Pagination Performance: Real-World Tests and Optimizations

When a table contains millions of rows, retrieving all records at once becomes very slow, especially as data grows, so pagination is required. This article explores several MySQL pagination methods and their optimizations.

Preparation

Table name: order_history Description: order history for a business

Main fields: unsigned int id, tinyint(4) type 37 columns, no large TEXT fields, 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. Three runs of the same query took about 8.9 s, 8.3 s and 8.4 s.

General pagination with LIMIT

The basic pagination uses the LIMIT clause:

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

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

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

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

A second argument of -1 means “to the end of the result set”.

Example:

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

This returns rows with id between 1001 and 1010. The query took about 3 s for 10 rows. Testing different row counts shows that queries returning fewer than 100 rows have similar execution times, while larger counts increase the time gradually.

Impact of offset size

Running the same query with offsets 100, 1,000, 10,000, 100,000 and 1,000,000 yields:

Offset 100: ~25 ms

Offset 1,000: ~77 ms

Offset 10,000: ~3.1 s

Offset 100,000: ~3.8 s

Offset 1,000,000: ~14 s

Large offsets cause the database to scan many rows, dramatically slowing the query.

Sub‑query optimization

First locate the id at the desired offset, then fetch the 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;

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

Execution times: 3.7 s, 1.3 s, 1.3 s, 3.7 s respectively. Using SELECT id instead of SELECT * speeds up the query about threefold.

ID‑range optimization

If id values are continuous, compute 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 time: 9‑15 ms. Alternative form:

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

Both achieve sub‑millisecond latency for small ranges.

IN‑clause optimization

Use a sub‑query to obtain a list of id s and filter with IN:

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 the IN sub‑query.

Temporary‑table approach

When id is not continuous (e.g., gaps or historical tables), store the pagination id s in a temporary table and query with IN. This can dramatically improve performance for tables with tens of millions of rows.

Guidelines for table IDs

It is advisable to add an auto‑increment id column to every table. For very large or sharded databases, use a distributed unique‑ID generator instead of relying on the auto‑increment column for global uniqueness.

Overall, locating the relevant id range first (via SELECT id or a temporary table) and then fetching the full rows can speed up pagination by several orders of magnitude.