CODEWITHSUNDEEP
mysql
Ashley Coolman
Ashley Coolman

Reputation: 11585

Fast mySQL leaderboard with player rank (& surrounding players)

I'm making a simple game, with leaderboard functionality (mySQL/PHP).

On game finish:

  1. To Server: Player's score
  2. From Server: Server, player's rank PLUS 5 players directly above & below them in rankings

I'm not particularky confident the sever will handle the expected player traffic - so I want to do this right.

Which of the following approaches would work (and be fastest)?

or have I missed a better solution?

Upvotes: 9

Views: 7014

Answers (1)

Johan
Johan

Reputation: 76641

ALTER TABLE
ALTER TABLE is to change the structure of the table.
You use it when you've made a mistake or you've changed your mind on how to do things.
If you dunno what it does, don't use it.

INDEX
An INDEX is to make sorting faster, always assign an index to columns that you use regularly in a WHERE clause or in an ORDER BY clause.
You can sort and select just fine without indexes, just slower.

RANKING
If you want to list highest ranking players on top then: 

SELECT field1, field2, ... FROM players ORDER BY score DESC LIMIT 25

Will give you the top 25 high score players, highest score first (it sorts in reverse order DESC from high to low)

FANCY RANKING

SELECT @rank:= 0; -- <<-- First run this query!

SELECT @rank:= @rank + 1 as rank, s.* FROM (
  SELECT field1, field2, ... FROM players ORDER BY score DESC LIMIT 25  
) s; --<<-- than this one.

Oh and read up on basic SQL stuff.
Google for SQL tutorial

Good luck.

Upvotes: 7

Related Questions