How to Speed Up MySQL Pagination: Practical Optimizations and Benchmarks

Preparation

When a table contains millions of rows, retrieving all records at once is very slow, especially as the data volume grows. The article uses a table order_history (37 columns, primary key id, 5,709,294 rows, MySQL 5.7.16) for testing.

General Pagination with LIMIT

The basic pagination uses the LIMIT clause:

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 specifies the row count.

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

Initial offset is 0, not 1.

Example:

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

This returns rows with id between 1001 and 1010.

Benchmark (three runs) for different LIMIT values:

1 row: ~3 000 ms

10 rows: ~3 050 ms

100 rows: ~3 150 ms

1 000 rows: ~3 450 ms

10 000 rows: ~3 800 ms

When the record count is below 100, query time is almost unchanged; larger result sets increase latency noticeably.

Impact of Offset

Tests with increasing offsets show a sharp rise in execution time once the offset exceeds 100 000 rows.

Offset 100: ~25 ms

Offset 1 000: ~77 ms

Offset 10 000: ~3 100 ms

Offset 100 000: ~3 800 ms

Offset 1 000 000: ~14 500 ms

Because MySQL scans from the first row, larger offsets cause slower queries.

Sub‑query Optimization

First locate the offset position's id, then fetch rows based on that id. Suitable when id is auto‑incrementing.

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;

Benchmark:

Statement 1 (SELECT *): 3 674 ms

Statement 2 (SELECT id): 1 315 ms

Statement 3 (SELECT * with sub‑query id): 1 327 ms

Statement 4 (original LIMIT): 3 710 ms

Using SELECT id instead of SELECT * speeds up the query by about three times.

ID‑Range Optimization

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

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

Execution time: 9‑15 ms.

Another form:

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

Using IN with a sub‑query is also possible, but some MySQL versions do not allow LIMIT inside the IN clause.

Temporary Table Optimization

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

Notes on Table IDs

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

Overall, locating the id (or another indexed column) first and then fetching the full rows can speed up pagination by several times.