On Wed, 2 Mar 2016 14:12:04 +0100 Clemens Ladisch <clemens at ladisch.de> wrote:
> > https://www.sqlite.org/lang_select.html talks about LIMIT & OFFSET, > > without mentioning that is a bad idea. > > Neither does it mention that it is a good idea. > > > can I do that or not (will it become sluggish if I do that) ? > > When you use large OFFSET values, the database must compute all these > rows before throwing them away. Can anyone describe a situation for which this style of LIMIT & OFFSET is advisable from the application's point of view? (The DBMS costs are obvious enough.) >From first principles, if the application has OFFSET, it might has well keep the connection handle instead, and just stept through the next LIMIT rows. What put the DBMS throught through the work of re-executing the query, when sqlite3_step is at the ready? My guess is the answer is going to have something to do with a web framework, and the cost of maintaining open connections, and the fact that OFFSET can be stored in a cookie but a handle cannot. I wonder about that trade-off in general, because some fraction of discarded cursors will be reconstituted as new (inefficient) queries. And for SQLite in particular, it's hard to imagine so many clients that discarding processes is better than letting them consume virtual memory until they time out. But rather than speculate, I'd be interested to hear of real motivations. thanks. --jkl
