3 ms·
That's great if you need to do some kind of crawl through the DB for background work, but fails to support key elements (filtering, non-primary-key sorting) tha
by awj 9y ago
That's great if you need to do some kind of crawl through the DB for background work, but fails to support key elements (filtering, non-primary-key sorting) that are probably hard requirements at the API level.
- i_s 9y agoYou can still support arbitrary filters and ordering, as long as you always sort by the ID last. The nice thing is the queries actually get more and more efficient as you approach the last page. (The normal approaches have the same cost across all pages, or even get more expensive toward the latter pages.) The only thing this approach does not support is jumping to an arbitrary page number.
- nitely 9y agoNot really: SELECT * FROM sales WHERE (sale_date, sale_id) < (?, ?) ORDER BY sale_date DESC, sale_id DESC FETCH FIRST 10 ROWS ONLY This is a postgresql example. See use-the-index-luke[0] for a generic approach. [0] http://use-the-index-luke.com/sql/partial-results/fetch-next-page http://use-the-index-luke.com/sql/partial-results/fetch-next...