> Given that even with LIMIT 2, the entire table's data might be read into > memory, what's the fastest/lightest method of finding the id of the next (or > previous) record ?
I believe your conclusion here is slightly wrong. SQLite reads whole table into memory and processes all rows only if it doesn't have index and it needs to do in-memory sorting before it will be able to understand which 2 rows to return. When SQLite uses index for query it doesn't scan all rows. Concerning next or previous record: how do you use LIMIT for that anyway? I don't see any decent way except some complicated nested queries which would be better performed in the hosting language. Pavel On Tue, Dec 14, 2010 at 12:09 PM, Simon Slavin <slav...@bigfraud.org> wrote: > I recently found out that when you use LIMIT in SQLite the engine still > processes all applicable records even if it only has to return the number you > asked for. I suspect that this makes something I used to do inefficient. So > let me ask for what I want and see what the list comes up with. > > I start with three things: > > * an arbitrary table which definitely has an explicitly defined field 'id' > which is always an alias to SQLite's use of 'rowid'. The table may have tens > of thousands of rows. > > * the value of 'id' of a particular record > > * an ORDER BY string that can be applied to this table, e.g. 'surname,age > DESC' > > I can guarantee that there is an index on whatever my ORDER BY string is. > > What I want: to be able to find the ids of the records before and after the > one I have. > > I have read <http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor>. My > problem is that my 'ORDER BY' string isn't known at programming time, it's > derived from user input during runtime and may include multiple columns, each > one ASC or DESC. > > Given that even with LIMIT 2, the entire table's data might be read into > memory, what's the fastest/lightest method of finding the id of the next (or > previous) record ? I cannot scan the entire table at the beginning of some > procedure and store the IDs: another user might add or remove records at any > time and I need the answer that applies when I execute the > find-the-next-record command. > > Am I doomed just to use LIMIT anyway ? > > Thank you, list. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
