You are viewing a single comment's thread from:
RE: What is cursor (seek) database pagination and how it is different from offset pagination?
What is more presenting only parts of the data reduces database stress (small numbers of rows has to be computed) and network usage (small numbers of rows has to be fetched from database to back-end application and then presented to the user).
I believe it is only partly true. Whenever you use ORDER BY clause - all rows that matches given filters (with WHERE clause) has to be computed by database engine. Only after that engine has to put them internally in right order and then - can selects part of them according to given LIMIT and OFFSET. So this only true when we are considering only profit from fetching smaller number of records.
Not quite. You can perform the tests yourself on bigger table. The bigger the OFFSET the bigger the delay on returning data. This is due to fact that it's easier for database to pick only 20 top rows than order whole table to get 20 from the middle. Using simple thought experiment: what is the sorting cost if you pick only top 20 elements? How would you implement this? It's complexity is less than n*20, right? In the meantime worst case scenario of sorting whole table could be even n^2. Mature databases uses the fact of LIMIT used for sorting optimisation.