Next/Prev Pagination Done Right

We all use pagination of some sort in our programs – but it turns out that the majority of us might be doing it wrong!

Consider the following Comments table:

If you wanted to page the above table you’d probably do something like…

SELECT * FROM Comments
WHERE post = @postID
ORDER BY id DESC
LIMIT 0 10;

And…

SELECT * FROM Comments
WHERE post = @postID
ORDER BY id DESC
LIMIT 10 10;

For the second page, and so on. However, what happens if another user adds a comment before the second page is loaded? You’d find the last comment on page 1 repeated at the top of page 2! Or if a comment from page 1 is deleted, you’d miss a possibly important comment at the top of page 2. I have actually noticed this behaviour myself in the YouTube app for Android.

The solution to this minor problem is quite simple; use the date field of the last comment as a start point (or pivot) for the limit statement:

SELECT * FROM Comments
WHERE post = @postID
AND date < @startDate
ORDER BY id DESC
LIMIT 10;

The above statement will only select rows older than the last comment, and then take 10 of them. The problem with the first method is that using the row offset in

LIMIT 10 10

is static, and the database table is not. Using a relative value such as the timestamp allows you to effectively track the position of the row in the table.

But Beware!

This is not a magic fix for all pagination (as the title suggests). What happens if the user wants to jump to page 3? Or the last page? This method only works when the pivot point is setby the select statment. By this I mean that you know the pivot point for the next prev/next request when you make the select (it's the last row).

If however you only need to support next/prev operations (such as in an infinitely scrolling list in mobile development) this method is ideal!

Source: https://coderwall.com/p/lkcaag