CODEWITHSUNDEEP
mysqldatabase
prabodhtiwari
prabodhtiwari

Reputation: 228

Database approach for Game Leaderboard

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

user_ max_score

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?

Upvotes: 1

Views: 1958

Answers (1)

Cyberience
Cyberience

Reputation: 1078

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]

Upvotes: 0

Related Questions