MySql - Ranking so it only needs to be done once

Question

By reading around, this seems to be a reasonable way of ranking people.

The table:

UserId (int primary key)
Name (varchar(100))
Score1 (int highest scores rank 1)
Score2 (int, if Score1s are equal, the person with the highest Score2 trumps all other equal Score1s)
Rank (int this is what I want to fill)

The Rank will only change about once a day. However, the page will likely be viewed maybe 1000 times a day. So instead of getting the database to calculate the rank for each of the 1000 visits, I thought it would be best to just fill the 'Rank' column once.

How can I include an UPDATE to fill the Rank column, using my chosen way to rank people. (Note if two people are ranked 5, then the next rank is 7.)

SELECT UserId, Name, Score, Rank FROM
    (SELECT UserId, Name, Score1, Score2
        @curRank := IF(@prevScore1 = Score1 && @prevScore2 = Score2, @curRank, @incRank) AS Rank, 
        @incRank := @incRank + 1, 
        @prevScore1 := Score1,
        @prevScore2 := Score2
       FROM players p,
       (SELECT @curRank :=0, @prevScore1 := NULL, @prevScore2 := NULL, @incRank := 1) r 
       ORDER BY Score1 ?????
    )
s

The Questions are:

1. How do I UPDATE Rank in the table within this SQL statement?
2. What do I do in the ????? ORDER BY statement to make sure it orders by Score1 first then when these are equal it orders by Score2 second?

Answer 1

You're almost there: Add order by Score1, score2, and enclose it with an update statement:

update players, 
    (SELECT UserId, Name, Score1, score2, Rank FROM     
        (SELECT userid, Name, Score1, Score2,         
        @curRank := IF(@prevScore1 = Score1 && @prevScore2 = Score2, @curRank, @incRank) AS Rank,          
        @incRank := @incRank + 1,          
        @prevScore1 := Score1,         
        @prevScore2 := Score2        
        FROM players p,        
        (SELECT @curRank :=0, @prevScore1 := NULL, @prevScore2 := NULL, @incRank := 1) r         
        ORDER BY Score1, score2     )x) s 
    set players.rank=s.rank where players.userid=s.userid;

This is the final result on a sample table:

mysql> select * from players order by score1, score2;;
+--------+--------+--------+--------+------+
| userid | name   | score1 | score2 | rank |
+--------+--------+--------+--------+------+
|      2 | Nathan |     16 |     13 |    1 |
|      3 | Steve  |     22 |     14 |    2 |
|      4 | James  |     22 |     14 |    2 |
|      1 | John   |     95 |     12 |    4 |
|      5 | Alain  |    168 |     16 |    5 |
+--------+--------+--------+--------+------+
5 rows in set (0.00 sec)

I'll upvote the question because it has the big part of the solution.

Answer 2

Don't update rank in the main table. Instead, put it into a separate table that you rebuild every night.

(You can use the inner part of Jehad's answer for building the table.)

Furthermore, do it this way to avoid any 'downtime':

CREATE TABLE new ...;
INSERT INTO new ...;
RENAME TABLE Ranks TO old, new TO Ranks;
DROP TABLE old;

Once you have perfected this, you may find the you can recalculate the Ranks hourly with no apparent impact.

Doing it this way minimizes impact on everything else going on in the system. It is less invasive than the UPDATE, and all ranks are moved into place simultaneously and instantly.