Skip to content

Keyset pagination

Keyset pagination (also known as the "seek method") is a database query optimization technique designed to improve performance when retrieving large datasets, particularly for deep pagination.^[600-developer-database-mysql.md]

Mechanism

Instead of using an OFFSET clause which skips a specified number of rows, keyset pagination utilizes a unique identifier—typically the primary key (id)—to navigate the dataset^[600-developer-database-mysql.md]. When requesting the next page of results, the application passes the ID of the last record from the previous page as a parameter^[600-developer-database-mysql.md]. The database then queries for rows where the ID is greater than this reference value^[600-developer-database-mysql.md].

Performance Characteristics

This method addresses the performance degradation inherent in standard offset-based pagination, where query speed slows down significantly as the offset value increases^[600-developer-database-mysql.md]. By leveraging the ordered index on the ID column, the database can jump directly to the correct starting position, avoiding the need to scan and discard preceding rows^[600-developer-database-mysql.md].

SQL Example

The implementation typically involves a condition on the primary key:

SELECT * FROM table WHERE id > 3000000 LIMIT 10;

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

  • [[Pagination]]
  • [[Offset pagination]]
  • [[Database indexes]]

Sources

  • 600-developer-database-mysql.md