10 ms·
This is a smart solution to the problem, if you must use OFFSET. That said, if you can avoid OFFSET, do, as it's typically considerably slower than a simple ind
by ajsharp 4y ago
This is a smart solution to the problem, if you must use OFFSET. That said, if you can avoid OFFSET, do, as it's typically considerably slower than a simple indexed range query.
If you're OFFSET-ing, say, 1000 records, I believe the database needs to load those 1000 records in some way, to exclude them from the result set (it's possible mysql does this differently than postgres). With a ranged query and a cursor (e.g. select * from tweets where created_at > CURSOR LIMIT 20) is, generally, more efficient. But cursored range queries don't make sense in a lot of cases and often come with some additional complexity in the code.
- Rapzid 4y agoSure but as soon as you start sorting and that column isn't the same as your range clause column and your indexes are perfect.. Sortsville.
- ajsharp 4y agoYep. It's brittle but if you need offset LOTS of records (e.g. a timeline) it's prob the only way to do it semi-scalable without storing the index somewhere else (redis etc)
- ajsharp 4y agoA much more complete post covering cursor-based pagination: https://www.moderntreasury.com/journal/shifting-our-apis-pagination-method-from-offset-to-cursor-based https://www.moderntreasury.com/journal/shifting-our-apis-pag...