MySQL Pagination Query Optimization Techniques and Performance Testing

When a table contains millions of rows, retrieving all rows at once becomes very slow, especially as data volume grows, so pagination queries are required. This article discusses several MySQL pagination methods, testing setups, and performance results.

Preparation

Table name: order_history Description: order history table for a certain business

Main fields: unsigned int id, tinyint(4) type Field details: 37 columns, no large TEXT fields, max varchar(500), id is indexed and auto‑increment

Data volume: 5,709,294 rows

MySQL version: 5.7.16

Testing was performed on this table; a simple SELECT COUNT(*) FROM orders_history; returns 5,709,294 rows. Three baseline queries each took roughly 8.3–8.9 seconds.

General Pagination

Typical pagination uses the LIMIT clause:

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

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

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

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

A second parameter of -1 means retrieve 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 rows with id between 1001 and 1010 (inclusive). Using ORDER BY id with LIMIT 10000,10 yields similar results. Performance for 10‑row queries dropped to about 3 seconds per execution.

Further tests varying the number of rows (1, 10, 100, 1,000, 10,000) showed query time increasing with result set size, while offset size had a larger impact when it exceeded 100,000 rows.

Subquery Optimization

This method first locates the offset id and then retrieves rows based on that id, which 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;

Timings: first query ~3.7 s, second ~1.3 s, third ~1.3 s, fourth ~3.7 s. Using SELECT id instead of SELECT * speeds up the query roughly threefold.

ID Range Optimization

If id values are continuous, calculate the range for the desired page and query with BETWEEN (or >=) directly:

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

Execution time: 15 ms, 12 ms, 9 ms – a dramatic improvement.

Alternative forms:

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

Using IN with a subquery (e.g., from another table) is also possible, though some MySQL versions do not allow LIMIT inside an IN clause.

select * from orders_history where id in (select order_id from trade_2 where goods='pen') limit 100;

Temporary Table Optimization

When id is not strictly continuous (e.g., due to data gaps), a temporary table can store the pagination id list, and the main query can use IN to fetch rows, greatly improving speed for tens of millions of rows.

About Table IDs

It is advisable to add an auto‑increment id column to every table. For sharded or very large tables, use a distributed unique‑id generator instead of the native auto‑increment, and store that identifier in a separate column. First locate the id range (or index) and then fetch the full rows, which can speed up queries by several times.

The author welcomes corrections and suggestions.