StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POLeaderboard design and performance in oracle
text
Body
copied!<p>I'm developing a game and I'm using a leaderboard to keep track of a player's score. There is also the requirement to keep track of about 200 additional statistics. These stats are things like: kills, deaths, time played, weapon used, achievements gained and so on.</p> <p>What players will be interested in is is the score,kills,deaths and time played. All the other stats are not necessarily needed to be shown in the game but should be accessible if I want to view them or compare them against other players. The expected number of players to be stored in this leaderboard table is about 2 million.</p> <p>Currently the design is to store a player id together will all the stats in one table, for instance:</p> <p><code>player_id</code>,<code>points</code>,<code>stat_1</code> .. <code>stat_200</code>,<code>date_created</code>,<code>date_updated</code></p> <p>If I want to show a sorted leaderboard based on points then I would have to put an index on points and do a sort on it with a select query and limit the results to return say 50 every time. There are also ideas to be able to have a player sort the leaderboard on a couple of other stats like time played or deaths up to a maximum of say 5 sortable stats.</p> <p>The number of expected users playing the game is about 40k concurrently. Maybe a quarter of them, but this is really a ballpark figure, will actively browse the leaderboard, the rest will just play the game and upload their scores when they are finished. </p> <p>I have a number of questions about this approach below:</p> <ol> <li><p>It seems, but I have my doubts, that the consensus is that leaderboards with millions of records that should be sortable on a couple of stats don't scale very well in a RDBMS. Is this correct ?</p></li> <li><p>Is sorting the leaderboard on points through a select query, assuming we have an index on it, going to be extremely slow and if so how can I work around this ?</p></li> <li><p>Should I split up the storing of the additional stats that are not to be sorted in a separate table or is there another even better approach ?</p></li> <li><p>Is caching the sorted results in memory or in a separate table going to be needed, keeping the expected load in mind, and if so which solutions or options should I consider ?</p></li> </ol> <p>If my approach is completely wrong and I would be better of doing things like this in another way please let me know, even options like NoSQL solutions in cloud hosting environments are open to be considered.</p> <p>Cheers</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload