On Tue, Sep 28, 2010 at 07:41:03AM -0500, Black, Michael (IS) scratched on the wall: > If performance of the query is a concern than add an additional > field for keeping the "next" and update it with insert/delete triggers.
Using an INTEGER PRIMARY KEY will provide optimal performance. It just happens that because a B*Tree is a *tree*, you get O(logN) performance. As Igor said, nothing in a table lookup is O(1), so even if you have the ROWID of the row you want (by adding one, looking for the next one, or from a column value), the lookup is still O(logN). -j > Cory Nelson <phro...@gmail.com> wrote: > > On Tue, Sep 28, 2010 at 3:32 AM, Ian Hardingham <i...@omroth.com> wrote: > >> If I have a table with a PRIMARY KEY AUTOINCREMENT id column, and I have > >> an id for a row, is there an Order(1) method of selecting the next row? > >> > >> I can't necessarilly guarantee that id + 1 exists, as it may be deleted. > > > > Try WHERE id>previousid ORDER BY id LIMIT 1. > > Note that this is O(log N), not O(1). There is no O(1) method to do > this (or pretty much anything else in SQLite), to my knowledge. > -- > Igor Tandetnik -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
