Pagination Based on Auto-Incrementing Primary Keys

Applicability: Use when the primary key is auto-incrementing and sequential, and results are sorted by the primary key.

Example query:

SELECT * FROM users LIMIT 90000, 5;

This query retrieves 5 records starting from the 90,001st row. The LIMIT clause works by scanning the first 90,005 rows, discarding the initial 90,000, and returning the remaining 5. As the offset increases, performance degrades because the database must scan more rows, often resulting in a full table scan (type: ALL) evenif a primary key index exists.

Optimization:

SELECT * FROM users WHERE user_id > 90000 LIMIT 5;

This optimized version uses an index range scan on user_id, significantly reducing scanned rows and improving efficiency. However, it requires that the primary key is strictly auto-incrementing and sequential, and results are sorted by the primary key. If record are deleted (e.g., row 90002 is removed), this query might skip intended rows, leading to inconsistent results compared to the original query.

Pagination Based on Non-Primary Key Sorting

Example query:

SELECT * FROM users ORDER BY username LIMIT 90000, 5;

If username is not indexed, the database may perform a filesort, scanning the entire table and sorting in memory, which is inefficient. Even with an index on username, the optimizer might avoid it if scanning the index and performing lookups for non-indexed columns is costlier than a full table scan.

Optimization:

SELECT u.* FROM users u
INNER JOIN (
    SELECT user_id FROM users ORDER BY username LIMIT 90000, 5
) AS sub ON u.user_id = sub.user_id;

This approach uses a derived table to first retrieve primary keys (user_id) sorted by username with the pagination limit. The outer query then joins on these keys to fetch the full rows. By limiting the sorted result to primary keys only, it leverages covering indexes more effectively, reducing the need for filesorts and minimizing row lookups. This improves performance while maintaining the same result set as the original query.