Range-based pagination¶

Range-based pagination is a database query optimization technique designed to improve performance when retrieving large datasets, particularly for deep pagination where standard offset-based methods become inefficient.^[600-developer-database-mysql.md]

Performance Context¶

In standard SQL implementations using LIMIT and OFFSET, query speed degrades significantly as the offset value increases.^[600-developer-database-mysql.md] This occurs because the database must scan and discard all preceding rows before reaching the desired data segment.^[600-developer-database-mysql.md]

To mitigate this, range-based pagination utilizes a conditional search on a unique, indexed column—typically the primary key—rather than a row count offset.^[600-developer-database-mysql.md]

Implementation Methods¶

Basic ID Filtering¶

The most direct form of range-based pagination passes the ID of the last record from the previous page as a parameter to the next query^[600-developer-database-mysql.md]. This allows the database to seek directly to the starting point, using a query structure similar to the following:

SELECT * FROM table WHERE id > 3000000 LIMIT 10;

This method avoids the computational overhead of calculating an offset, resulting in faster execution times for deep pages.^[600-developer-database-mysql.md]

BETWEEN Operator¶

For scenarios where continuous integer ranges are available, the BETWEEN operator offers a highly optimized solution^[600-developer-database-mysql.md]. This explicitly defines the start and end boundaries of the requested data:

SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;

Comparison with Other Optimizations¶

Range-based pagination is distinct from delayed association (also known as "deferred join"), another technique used to optimize deep pagination^[600-developer-database-mysql.md]. Delayed association involves querying only the indexed ID column first using an offset, and then joining the result back to the main table to retrieve full row data^[600-developer-database-mysql.md].

While delayed association reduces the overhead of "looking back" for non-indexed columns during the scan^[600-developer-database-mysql.md], range-based pagination generally provides superior performance by eliminating the offset scan entirely^[600-developer-database-mysql.md].

Sources¶

• 600-developer-database-mysql.md