Pagination in MySQL

Every once in a while I need to implement pagination in an application. The approach using OFFSET/LIMIT is straightforward, getting the third page of a product list with 20 items per page is as simple as:

SELECT *
FROM products
ORDER BY id
LIMIT 20 OFFSET 40;

This works great for the first handful of pages, but as the page count grows the query gets slower and slower. This is because the SQL engine cannot directly land on the first row of the page. It needs to go through all rows starting from the first one in the first page just to count them. Even with a proper index, the engine still needs to read many system pages in the index itself.

The alternative, often called keyset pagination, does not count pages but remembers the ID of the last row seen in the previous page.

SELECT *
FROM products
WHERE id > 560
ORDER BY id
LIMIT 20;

Now the SQL engine can use an index to land directly on the row 560 and start retrieving the next 20 rows. It's much faster but comes with a big caveat: it is not possible to easily jump from one page to another page that does not come just after.

More information Use The Index, Luke!.