On Wed, Jun 8, 2011 at 10:18 AM, Marco Bambini <ma...@sqlabs.net> wrote:
> I have a table foo with N rows and I want to know the offset of the row > with rowid X inside that table. > What query/strategy should I perform? > > I assume that a brute force algorithm should be > 1. SELECT * FROM foo ORDER BY rowid; > 2. loop inside the recordset until X is found incrementing a counter by 1 > but I am quite sure that should be a more elegant way. > > SELECT count(*) FROM foo WHERE rowid<=X The above gives an answer in linear time. It is theoretically possible to add a little extra metadata to the btree nodes in order to compute the offset logarithmic time. But I deliberately decided not to included that metadata when I designed the SQLite btree file format since keeping that metadata current slows down write performance. > Thanks a lot. > -- > Marco Bambini > http://www.sqlabs.com > > > > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
