0

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

SomehowLuke
  • 69
  • 1
  • 11
  • This is a simple question about pagination. There are thousands of solutions out there - selecting just what's needed, selecting everything to json/xml, infinite scroll, hybrid solutions, etc. – Strawberry Aug 21 '15 at 11:16
  • Thank you for your comment. I was already searching for something like this but could not find the right tutorial. I have a usertable with points. When I select all with orderBy points i have place 1 to 1m automatically. But If I solve it with paging how can I find the offset I need because I would need the position from the general selection (let it be position 5530) and then build my offset out of it (5530/100 items each page = Page 55 => 55 * 100 = 5500, So Offset 5500 Limit 100.) But how do I get the position i need? – SomehowLuke Aug 21 '15 at 11:33

1 Answers1

0

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.

Nenad Mitic
  • 577
  • 4
  • 12