Offset or page number when paging
It's pretty trivial, but I noticed on SO that they use page numbers instead of offset. I know the difference is negligible (multiply the page number by lines on the page, or split the offset by lines on the page), but I'm wondering if it's recommended over another.
Some sites, like Google, of course use a more complex system because they have to track your actual searches. But I'm thinking of a simple site where it doesn't matter.
What's the recommended method?
source to share
Offsets are also useful for optimization when the result set you are paginating is very large.
This is because in some cases it allows you to do
WHERE my_sortorder >= (some offset)
LIMIT 10
but not
LIMIT 10 OFFSET 880
which is less effective. The index can let you go to all rows matching my_sortoder> = some offset, but when you use OFFSET with LIMIT, it needs to find and scan all 880 previous rows first.
source to share
Using a combination of limit
and is offset
best because you no longer need to compute a new page number to change your limit.
For example, with 20 objects per page, assuming that page 1 returns objects with index 0 - 19, if you are on page 2, then you should look at objects with index 20 - 39. If I now decide to change my limit to 10, the most common the behavior will be displaying objects with index 20 - 29.
Doing the above behavior will require you to recalculate which page you should be enabled on using the new limit. Above, if you must change the limit to 10, but keep the page number at 2, then you will display objects at index 10-19. You will need to recalculate the page number to be 3 so that the objects are 20 - 29.
My opinion, of course.
source to share