MySQL Pagination Optimization Techniques and Performance Testing

When a database table contains millions of rows, retrieving all records at once becomes slow, so pagination is essential. The article uses an order_history table (≈5.7 million rows, MySQL 5.7.16) to demonstrate and benchmark several pagination techniques.

Basic LIMIT Pagination

The standard approach uses the LIMIT [offset,] rows clause. Example: SELECT * FROM orders_history WHERE type=8 LIMIT 1000,10; This retrieves rows 1001‑1010. Benchmarks show query times around 3 seconds for small offsets, but time grows as the offset increases.

Offset Impact Test

Queries with larger offsets (e.g., 100 000, 1 000 000) exhibit dramatically higher latency, reaching up to 14 seconds, because MySQL scans from the beginning of the table for each offset.

Sub‑query Optimization

First fetch the target id using a sub‑query, then retrieve the desired rows by ID, which reduces scanning:

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 sub‑query version can be 3× faster than the plain LIMIT approach.

ID‑Range Optimization

If IDs are sequential, calculate the ID range for the page and query with BETWEEN or a simple >= condition:

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

This method yields query times of 9‑15 ms, dramatically improving performance for known ID ranges.

Temporary Table Optimization

When IDs are not continuous (e.g., due to deletions or sharding), store the page’s IDs in a temporary table and query using IN. This can also accelerate pagination on tens of millions of rows.

General Recommendations

• Prefer selecting only the id first, then fetching full rows. • Use ID‑range queries whenever possible. • Avoid large offsets with plain LIMIT. • Consider temporary tables for non‑sequential IDs. • For massive tables, use distributed unique ID generators instead of auto‑increment primary keys.

The author notes that the presented methods can speed up pagination by several times, though each has specific applicability constraints.