0

Recently I have developed mobile game (LAMP + memcached).

The game has player's score table. The table has member_id, name, score column.

I want to show leaderboard (global rank) to our user.

Just query SELECT * FROM score ORDER BY score DESC, and show the resultset.

But I don't think it is good way. If users reach 20 million, this query seem to be terrible. What is the best practice in this case? Could you give me some advice?

And how can I check specific user's rank? I want to show each user rank where they are.

Sami Kuhmonen
  • 30,146
  • 9
  • 61
  • 74
o242
  • 51
  • 1
  • 7
  • 3
    `SELECT * FROM score ORDER BY score DESC LIMIT 1,100` as long as your database is properly indexed, and your queries are "sensible", 20 million records isn't a problem.... trying to display 20 million scores on one web page would not be "sensible" for any number of reasons – Mark Baker Jan 14 '14 at 11:53

1 Answers1

0

Well, you don't want to display millions of users on the leader board do you? 

SELECT * FROM score ORDER BY score DESC LIMIT 10

Would select top 10

Further, if you want to display a users rank on for instance a profile page this should do:

SELECT COUNT(*) + 1 AS rank FROM score WHERE score > (SELECT score FROM score WHERE member_id = :member_id);

You simply add the current users member_id to the last query and it will count all rows ahead of him and add 1 to that.

NewInTheBusiness
  • 1,465
  • 1
  • 9
  • 14
  • `SELECT * FROM score ORDER BY score DESC` If there are 20 million user in the table, I think this query will affect bad performance. don't you? should I add index on score column? – o242 Jan 15 '14 at 04:39
  • If you're counting on 20 million users, you must have indexes for good performance. – NewInTheBusiness Jan 15 '14 at 04:56