Stop using offset for pagination - Why it's grossly inefficient

Posted by fyzic@reddit | programming | View on Reddit | 5 comments

[-]

fuckyeahgirls@reddit

What if you're ordering by something that isn't a unique field?

This post seems to imply it's a matter of laziness on the part of framework developers which seems like a pretty uncharitable explanation.

[-]

fiskfisk@reddit

The limitation is that the unique key only has to be present in the sort criteria to break ties, not that it has to be the main sort criteria.

[-]

Say I have a product table and I'm sorting by name. How does it work then? I can add the primary key as a second sorting field but that doesn't make sense because the first 3 values might be [5, 99, 1], and then the 4th row on the next page might be -99999999999 so your id > 1 expression doesn't do what you want it to. The whole point of sorting is that the order of the returned set has no relationship to the order in which they were added.

Also this is completely ignoring situations where you aren't using auto-incrementing ids.

[-]

AngusMcBurger@reddit

The key is that you only use the id in tie-breaking situations (as in, only check id if name is the same), so it looks like

WHERE name > :name OR (name = :name AND id > :id)
ORDER BY name, id

If your database supports tuple comparisons, you can write the much nicer

WHERE (name, id) > (:name, :id)
ORDER BY name, id

[-]

ValuableInitiative58@reddit

Very helpful, attempting to follow that article I had added in a spurious PK filter on a query with 2 different columns being used to sort.

I was about to give up and resort to using offset but this solves the problem, thank you :)