CODEWITHSUNDEEP
language-agnosticpagination
Darryl Hein
Darryl Hein

Reputation: 144957

Offset vs page number when doing pagination

This is pretty trivial, but I noticed on SO that instead of an offset they are using page numbers. I know the difference is minor (multiply the page number by rows on a page or divide offset by rows on a page), but I'm wondering if one is recommended over the other.

Some sites, like Google, of course use a more complicated system because they need to track your actual search. But I'm thinking for a simple site where this doesn't matter.

What is the recommended technique?

Upvotes: 16

Views: 15305

Answers (4)

R...
R...

Reputation: 2570

I believe offset and limit is cleaner.

if we consider a certain record (say 10th one) as the start of a page, with offset there is a very straight forward and easy to follow logic . offset=10 and you can set the limit to any page size.

Whereas page number depends on page size and you have N different ways to start your page with that record and you have to tie that to page size. you can choose pageSize=10&pageNumber=2 all the way to pageSize=1&pageNumber=10 which is not as clear cut as offset and limit

Upvotes: 0

thomasrutter
thomasrutter

Reputation: 117333

Offsets are useful for optimization when the result set you are paginating is very large.

This is because in some cases it allows you to do a

WHERE my_sortorder >= (some offset)
LIMIT 10

rather than a

LIMIT 10 OFFSET 880

which is less efficient.

In the first case you can use an index to jump directly to the start of your results (internally it will be a binary search). In the second case you are fetching and discarding 880 rows before arriving at the rows you want. If the fetch can be satisfied by an index it won't need to load all columns from that first 880 rows but you're still retrieving that many rows at least in the index.

That said, if the column you're sorting on can have duplicate values going to a particular offset may need the creation of a new column purely with unique sort values, but the effort can be worth it if paging deep into a sorted result set is a goal.

Upvotes: 9

kbuilds
kbuilds

Reputation: 1061

Using a combination of limit and offset is best because it does not require any further math to figure out the a new page number if you 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 be looking at objects with index 20 - 39. If I now decide to change my limit to 10, the most common behavior would be to display objects with index 20 - 29.

Accomplishing the above behavior would require that you re-calculate which page you are supposed to be on, using the new limit. Above, if you were to change the limit to 10, but keep the page number at 2, then you would be displaying objects with index 10 - 19. You would need to re-calculate the page number to be 3, so that objects 20 - 29 were displayed.

My opinion, of course.

Upvotes: 8

Stefan Mai
Stefan Mai

Reputation: 23939

Use offsets. If you choose to allow variable (or user-defined) number of results per page, the coding is easy.

Upvotes: 11

Related Questions