1

I am working on a sever app that has multiple games and should be able to handle millions of players. Each game needs a leaderboard and must be able to show the player's current position and top 10 players, as well as the positions of the players friends.

Currently I am using two tables.

user_score

  • id
  • user_id
  • game_id
  • score
  • created_at

user_ max_score

  • id
  • user_id
  • game_id
  • max_score
  • created_at
  • updated_at

Whenever a user plays any game for the first time data is inserted in the first and second table. If the user plays a second time and scores more than their previous score we insert data into user_score table and update user_max_score table with the new max_score.

And for getting the rank of the user I fire query like this.

SET @i=0;
SELECT id, user_id, max_score, @i:=@i+1 AS rank 
FROM user_max_score WHERE game_id = $gameId
ORDER BY max_score DESC, updated_at ASC;

Is there any better approach than this?

Admiral Noisey Bottom
  • 114
  • 1
  • 10
prabodhtiwari
  • 208
  • 1
  • 2
  • 9
  • Drop the `user_max_score` table and do something like `SELECT user_id, MAX(score) AS max_score FROM user_score GROUP BY user_id` (see http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – AmazingDreams May 18 '16 at 07:48
  • been a while , how did u solved this problem @dgoku ? – Sukhmeet Singh May 14 '18 at 11:25

1 Answers1

0

One approach I am using is a single table concept. All scores posted, and then you can consolidate scores in the back ground to a summary table. This maintains front end performance, and you can handle back ground consolidation process for summary information into a cache table for the accumulated scores, in my situation I am using goLang and couchbase tables, but in mySql, you will find add a row to be much faster than upsert, where you find a record and then update. The current database modals are changing rapidly to the noSQL format due to size and scalability, the old SQL modal struggles for performance and replication get complex, for a single DB system and small user base, your are fine.

Request URL

HTTP POST   http://[some dns]:8080/v1/post_score

Request Payload 

{       
    'user_id'  :    '[some-user-id]', 
    'game_id'  :    '[some-game-id]',
    'score'    :    [value],
    'duration' :    [time played],
    'level'    :    [level reached],
    'stars'    :    [stars gained],
    'os'       :    '[os identified]'
}

Additional fields in the Database, captured by the user agent.

'ip' : [detected user ip],
'ts" : [timestamp from the server point of view]
Cyberience
  • 972
  • 10
  • 15