Geek Answers Handbook

Player ranked mySQL quick table (and surrounding players)

I am making a simple game with leader functionality (mySQL / PHP).

At the end of the game:

  • To server: Player rating
  • From the server: server, player rating PLUS 5 players directly above and below them in the ratings

I’m not sure that it will respond to the expected traffic of the player, so I want to do it right.

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

  • Change table? (slowly when the table changes frequently? How to get the rank?)
  • Simple row counter (rank) with ORDER BY? Example
  • A simple row counter (rank) using a WHERE clause is faster? Example

or did i miss the best solution?

+5
source share
1

ALTER TABLE
ALTER TABLE - .
, , , .
, , .

INDEX
INDEX , , , WHERE ORDER BY.
> , .


:

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

25 , ( DESC )

FANCY

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.

SQL.
Google SQL tutorial

.

+3

More articles:


All Articles