MySQL Performance for Online Games Highscore Lists

Question

I have a question about making "Highscore-Lists".

Lets say I have an online game with 1.000.000 active users. Each user has points from 0 to X. Now, I want to show a ranking-list. It would be insane to show all million entries in one page so it is divided into Y pages (100 entries each page => 10.000 pages).

I am not really sure how to solve it.

1. The easiest way to do that would be loading all 1m entries

in one SELECT, get the result and find current user with a for loop and show that specific page. (but all other 999.900 entries will be saved in RAM eventhough its not showing up). For a page change I could just use the result data with no second database call. (So I don't care about point changes during that time)

SELECT UserName, UserID, Points FROM UserAccount ORDER BY Points;

2. My second idea was, to load each page individually but than I do not know

2.1 if it is really better performance 2.2 how to get the right start page because I only have the points of the user but not really his place

So how could I solve that problem. I dont really know what mysql can handle. Are more small calls better then one huge call. Can I even save huge result data? Second solution would update all changed points with each page change, though but i care more about performance then always uptodate list-data.

Thank you for your help! Markus

Answer 1

Use pagination. In SQL it's a "limit" clause:

SELECT UserName, UserID, Points FROM UserAccount ORDER BY Points LIMIT 0, 20;

The above query will return only the first 20 rows of the original selection.

You can pass page parameters via get, like this: highscore.php?page=1 or ?page=2 and so on.