Keyset pagination

In the past I have used the MySQL equivalent of pagination. In other words, the splitting up of a ResultSet into pages of a fixed number of entries, by means of using SQL¹.

It looks like the following:

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax, which I've used in the past.

Performance

Performance is a key point here, as MySQL requires the retrieval of the results in order to determine where the offset starts.

If the table is large, retrieval of pages at the end of the table are going to be extremely slow.

Solution

A better way to deal with this, is to not use an offset, but use the key of the last row of the previous page, and use that in the query for the next page.

Obviously this only works if the resultset is sorted.

For more references that explain this a lot better, see [2] and [3].

References

[1] MySQL 5.7 - 14.2.9. SELECT Syntax

https://dev.mysql.com/doc/refman/5.7/en/select.html

[2] Use the Index, Luke! - We need tool support for keyset pagination

http://use-the-index-luke.com/no-offset

[3] Use the Index, Luke! - Paging Through Results

http://use-the-index-luke.com/sql/partial-results/fetch-next-page