Hi, I have a table of players each with a score. I need to do 2 things:
1. select top n players sorted on score - this is easy, just select stuff order by score desc limit n. For efficiency, use an index on score. The top guy is rank 1, the next guy is rank 2, etc. I only need to do this once a day, so it doesn't have to be super fast. 2. determine the rank of an arbitrary player p. This is harder. I need to do it many times. I presently select count(*) + 1 where score is > score of player p. The problem is that #2 is too slow when I have 1M players in the table - around 80 ms (with an index on score). I need something much faster - a few ms is OK. I tried writing rank into the table as part of the leaderboard processing by doing a select order by score desc with an update for each row, but this is too slow (even using begin/commit). Would take around 5 hours for 1M players, but I need something that would take just minutes. Would appreciate any ideas. Thanks Tom -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-determine-player-s-leaderboard-rank-efficiently-tp77445.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
