Skip to content

Cursor-based pagination

Cursor-based pagination is a database query optimization technique designed to address performance degradation encountered when using traditional offset-based pagination for large datasets.^[600-developer__database__mysql__大数据分页优化.md]

In standard pagination, performance tends to decrease as the offset (the number of rows skipped) increases.^[600-developer__database__mysql__大数据分页优化.md] To mitigate this, cursor-based pagination uses a unique identifier—typically the primary key ID—from the last record of the previous page as a reference point.^[600-developer__database__mysql__大数据分页优化.md]

Instead of skipping rows, the query filters the dataset to retrieve records that appear strictly after the specified cursor value.^[600-developer__database__mysql__大数据分页优化.md]

SQL Implementation

The core implementation involves modifying the SQL query to check against the cursor value rather than calculating an offset.^[600-developer__database__mysql__大数据分页优化.md]

For example, if the ID of the last item on the previous page was 3000000, the query to fetch the next page would be:

SELECT * FROM table WHERE id > 3000000 LIMIT 10;

This method allows the database to utilize the index on the ID column directly, significantly improving retrieval speed for deep pagination.^[600-developer__database__mysql__大数据分页优化.md]

Sources

  • 600-developer__database__mysql__大数据分页优化.md