1

I have the following situation. I have a leaderboard that is backed by a mysql table.

An entry in the table table will look like.

username | score

When we are displaying the scoreboard we query for all entries sorted by score. Then we assign the users positions based their position based in the result set. For example if we query for the table and there are 3 results. We know result 1 is 1st position, result 2 is second position etc.

Users can submit scores and there is nothing new to compute till we query for the leaderboard.

However this leads to the problem where, we can't tell a single user what position they are in without querying for the entire database. Is there a way for mysql to store this information and make updates of new users fast?

Funzo
  • 1,190
  • 2
  • 14
  • 25
  • You could use count to see the position of the user with **SELECT count(*)+1 from table as t where t.score > (select score from table where user='username')** – nacho Sep 11 '19 at 15:11
  • The count(*)+1 is because if I am the one with highest score, the count(*) will return 0, so add 1 and my position will be 1 (first) – nacho Sep 11 '19 at 15:25
  • Maybe this would help? Seems like a similar problem. https://stackoverflow.com/a/3197174/589921 – Dara Java Sep 11 '19 at 15:25

0 Answers0