Re:Modern SQL
by simonw on Friday August 13, 2021 @03:05PM
(#28173053)
The problem is that when I implement pagination it's often because I want automated scripts to be able to export ALL of the data, which means they need to be able to paginate to the end of the results.
"SELECT * from table limit 100 offset 10000" requires the database to skip 10000 rows in order to get to the rows it needs to start returning - and then on the next page it has to do it again, using offset 10100.
If you're showing an interface to users you can implement an easy workaround: don't allow them to click past page 10, in which case OFFSET pagination only ever needs to loop through up to about 1,000 rows (depending on your page size).
Example from one of my projects: https://covid-19.datasettes.com/covid/ny_times_us_counties - it uses keyset (cursor) pagination so it's possible to efficiently export all 1,611,908 rows of data.
"SELECT * from table limit 100 offset 10000" requires the database to skip 10000 rows in order to get to the rows it needs to start returning - and then on the next page it has to do it again, using offset 10100.
If you're showing an interface to users you can implement an easy workaround: don't allow them to click past page 10, in which case OFFSET pagination only ever needs to loop through up to about 1,000 rows (depending on your page size).