0

Curious what's the most elegant way to implement leaderboard across multiple games with SQL database?

I have game G1, and players get different scores there (from 0 to 100) I have game G2, and players get different scores there too (but from 0 to 1000) I have game G3, and players get different scores there as well (from 1000 to 10000)

Probably shared players (but not necessary) How can I implement the leaderboard, so I can show top ranked 5 players across all the games and it'll not kill my DB with tons of SQL queries? I am talking about hundreds and potentially thousands request/second to this leaderboard.

I have few my own ideas, but neither of them seem elegant and simple enough...

alexeypro
  • 429
  • 2
  • 6
  • 12
  • What does it mean for a player to be ranked above another player in such an aggregated leaderboard? That the sum total of their scores is greater? That the mean of their scores is greater? That the sum of their normalised scores is greater? Something else? – eggyal Mar 28 '13 at 13:40
  • 1
    persistant database-connections, so the programming language used don't have to open database over and over again. – bestprogrammerintheworld Mar 28 '13 at 13:40

1 Answers1

0

One approach would be to put the 'standings' in a mem table and update it 1x/sec. Then query this for the 'display leaderboard' page. This way your server would only query the game rankings 1x / sec. The query to the 'leaderboard' table would be coming from memory anyway and would usually come from the query cache (if you have it enabled).

Since the standings is a derived table there isn't any reason to write it to disk.

Oh - and do what @bestprogrammerintheworld said - reuse your connections.

ethrbunny
  • 10,379
  • 9
  • 69
  • 131