MySQL pagination performance problem¶

In MySQL, pagination performance typically degrades as the data offset increases. The larger the offset, the slower the query speed becomes.^[600-developer-database-mysql.md]

The Problem with `OFFSET`¶

The performance bottleneck is largely due to the use of SELECT *. When a query selects all columns, MySQL must traverse the index to locate the specific rows defined by the offset, but it must then perform a "random read" (回行) to fetch the full data for every row along the path.^[600-developer-database-mysql.md] This process of reading data rows back from the table after finding the index entry introduces significant overhead as the offset grows.^[600-developer-database-mysql.md]

Optimization Strategies¶

Several strategies can be employed to mitigate these performance issues:

Restricting Offset¶

The simplest approach is to enforce business logic that forbids viewing pages too far back in the dataset, effectively capping the maximum offset.^[600-developer-database-mysql.md]

ID Range Filtering¶

If the previous page's maximum ID is available, the query can be rewritten to filter by that ID rather than calculating an offset.^[600-developer-database-mysql.md]

SELECT * FROM table WHERE id > 3000000 LIMIT 10;

Delayed Association¶

A more robust optimization involves using a Delayed Association strategy.^[600-developer-database-mysql.md] Instead of selecting all columns immediately, the query first retrieves only the IDs for the target range (which is fast because it only touches the index).^[600-developer-database-mysql.md]

SELECT id FROM table LIMIT 3000000, 10;

An INNER JOIN is then used to retrieve the full data for only those specific 10 rows.^[600-developer-database-mysql.md]

SELECT table.* 
FROM table 
INNER JOIN ( 
    SELECT id FROM table LIMIT 3000000, 10 
) AS tmp ON tmp.id = table.id;

This method significantly reduces I/O and typically keeps query response time within one second, even for large offsets.^[600-developer-database-mysql.md]

Between Ranges¶

If the IDs are sequential and known, using BETWEEN is an effective alternative^[600-developer-database-mysql.md]:

SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;

Sources¶

^[600-developer-database-mysql.md]