MySql - Ranking so it only needs to be done once

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:

• How do I UPDATE Rank in the table within this SQL statement?

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

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.

StackExchange Database Administrators Q#135693, answer score: 2