3 ms·
Markus Winand is a great SQL expert. I'm really grateful for everything that he shares with us. He writes well, explains us the differences between the differen
by olavgg 11y ago
Markus Winand is a great SQL expert. I'm really grateful for everything that he shares with us. He writes well, explains us the differences between the different SQL engines. And his books are fantastic references.
I highly recommend his "No to offset" tutorial http://use-the-index-luke.com/no-offset http://use-the-index-luke.com/no-offset
Should be common knowledge, but I still see offset being used way to often, even in core ORM frameworks :-(
- sanderjd 11y agoA summary: "don't use offset unless you need to be able to navigate to specific pages, in which case you have to use offset". That "navigate to specific pages" use case is pretty common.
- Gracana 11y agoIf page numbers are important to you, then you have to find a different method anyway because of the drifting issue.
- sanderjd 11y agoOr just accept that the page won't be exactly "right", but close. The solution proposed in the article doesn't have a "good enough" option for pages, as far as I can tell.
- elithrar 11y agoIf you can use cursor-based pagination, then definitely do so—but as you point out, you can't always do that. Further, unless you're using bigserials (i.e. incrementing ints), you can't check $last_seen_ID either. With UUIDs (whether v4 or a custom impl.) there's no way to check `WHERE id > $last_seen_id`.
- jeltz 11y agoYes, but people need to understand how expensive it is to implement.
- sanderjd 11y agoYeah, sorry if I seemed to suggest otherwise. I thought it was a good article, and it should be more widely understood that offset isn't a free lunch.