Re: [sqlite] Getting the location of a record in an Index
Hey Igor, thanks for the reply. Is this O(1) ? Ian On 09/09/2011 13:21, Igor Tandetnik wrote: Ian Hardingham wrote: Again, I have: eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo FLOAT) with: CREATE INDEX IF NOT EXISTS eloResultScore ON eloResultTable (elo DESC) If I have the id of a row in eloResultTable, I wish to find how far down the eloResultScore index it is (I basically want to find a player's rank when ordered by elo). Is there a way to do this? select count(*) from eloResultTable where elo>= (select elo from eloResultTable where id = ?); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the location of a record in an Index
On 9 Sep 2011, at 1:47pm, Ian Hardingham wrote: > I really want O(1), but selecting by elo DESC and then setting a ranking > column for all records seems to be very slow, even during a transaction. Any > tips for doing that fast? If you're trying to collect this information for many different records there no need to run that SELECT many times. Simply read your records ORDER BY old DESC and the first record will be ranked first, the second will be ranked second, etc.. There's no need to make SQLite do the numbering for you. If you need to store the ranking with the record, just read all the rowids into a big array, then use their position in the array to work out how to update the ranking fields. Also, you need to decide whether there's any point in storing these scores, or you can just calculate the score for a record when someone wants to see that particular record. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the location of a record in an Index
Thanks Igor. I really want O(1), but selecting by elo DESC and then setting a ranking column for all records seems to be very slow, even during a transaction. Any tips for doing that fast? Thanks, Ian On 09/09/2011 13:34, Igor Tandetnik wrote: Ian Hardingham wrote: Hey Igor, thanks for the reply. Is this O(1)? Or... I guess it's probably low-magnitude O(log n) ? It's O(n). There's no shortcut to count(*), it walks through and counts all the records satisfying the condition. A B-tree doesn't provide a fast way to obtain a number of all the elements smaller than a given element. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the location of a record in an Index
Ian Hardingham wrote: > Hey Igor, thanks for the reply. > > Is this O(1)? Or... I guess it's probably low-magnitude O(log n) ? It's O(n). There's no shortcut to count(*), it walks through and counts all the records satisfying the condition. A B-tree doesn't provide a fast way to obtain a number of all the elements smaller than a given element. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the location of a record in an Index
Hey Igor, thanks for the reply. Is this O(1)? Or... I guess it's probably low-magnitude O(log n) ? Ian On 09/09/2011 13:21, Igor Tandetnik wrote: Ian Hardingham wrote: Again, I have: eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo FLOAT) with: CREATE INDEX IF NOT EXISTS eloResultScore ON eloResultTable (elo DESC) If I have the id of a row in eloResultTable, I wish to find how far down the eloResultScore index it is (I basically want to find a player's rank when ordered by elo). Is there a way to do this? select count(*) from eloResultTable where elo>= (select elo from eloResultTable where id = ?); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting the location of a record in an Index
Ian Hardingham wrote: > Again, I have: > > eloResultTable (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT, elo > FLOAT) > > with: > > CREATE INDEX IF NOT EXISTS eloResultScore ON eloResultTable (elo DESC) > > If I have the id of a row in eloResultTable, I wish to find how far down > the eloResultScore index it is (I basically want to find a player's rank > when ordered by elo). Is there a way to do this? select count(*) from eloResultTable where elo >= (select elo from eloResultTable where id = ?); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users