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
