Fast mySQL leaderboard with player rank (& surrounding players)

Question

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)?

• Alter Table? (slow when table is often modified? how to get rank?)
• Simple row (rank) counter with ORDER BY? Example
• Simple row (rank) counter using WHERE clause faster? Example

or have I missed a better solution?

Answer 1

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.