SQL Keyset Pagination
Pagination should have a stable order. OFFSET is easy to start with, but large offsets can make the database walk past many rows. Keyset pagination starts after the last seen sort key instead.
Understand the idea
This example shows the next two products after identifier 1. Because product_id is unique and ascending, the cursor needs only that identifier. Sorting by date plus identifier requires both values in the cursor and an equivalent comparison condition. Keyset pagination works well for next-page navigation, while arbitrary page-number jumps are a different requirement.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT product_id, name
FROM products
WHERE product_id > 1
ORDER BY product_id
LIMIT 2;
Read the result
The next page contains products 2 and 3. A following request would use 3 as the last seen identifier. The page does not depend on counting and skipping every earlier row.
Try one change
Write the following-page query using the last identifier from this result. Explain why deletion of an earlier product does not shift the cursor.