MySQL – Efficient Ranking to Avoid Repeated Calculations

MySQLrankupdate

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?

Best Answer

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.

Related Solutions

Update Ranking on MySQL Table

I address this question back on Jun 08, 2012 : Update rank on a large table

First, make sure you have an index on rank and last_rank in the rank table

Next, make sure you have a points index on the score table

If you do not have such indexes, run the following:

ALTER TABLE rank  ADD INDEX (rank);
ALTER TABLE rank  ADD INDEX (last_rank);
ALTER TABLE score ADD INDEX (points);

You are going to have to update the last_rank on the fly using an UPDATE...JOIN:

SET @r=0;
UPDATE (SELECT id score_id FROM `score` ORDER BY points DESC) A
    LEFT JOIN `rank` B USING (score_id)
    SET B.last_rank = B.rank,B.rank = @r:= (@r+1)
;

Here is a more paranoid version

DROP TABLE IF EXISTS score_rank;
CREATE TABLE score_rank
(
    id        INT NOT NULL AUTO_INCREMENT,
    score_id  INT NOT NULL,
    rank      INT,
    last_rank INT,
    PRIMARY KEY (id),
);
SET @r=0;
INSERT INTO score_rank (last_rank,rank,score_id)
    SELECT B.rank,@r:= (@r+1),B.score_id FROM
    (SELECT id score_id FROM `score` ORDER BY points DESC) A
    LEFT JOIN `rank` B USING (score_id)
;
UPDATE rank A INNER JOIN score_rank B SET
    A.rank = B.rank,
    A.last_rank = B.last_rank
;
DROP TABLE IF EXISTS score_rank;

Give it a Try !!!

CAVEAT

If the table is very large, it may take time. As I suggested in Update rank on a large table, you may want to limit the ranking to the first 10,000 when ranking frequently, and then do the fuill ranking maybe once a week or once a month.

Therefore, ranking top 10,000 should look this

SET @r=0;
UPDATE (SELECT id score_id FROM `score` ORDER BY points DESC LIMIT 10000) A
    LEFT JOIN `rank` B USING (score_id)
    SET B.last_rank = B.rank,B.rank = @r:= (@r+1)
;

or this

DROP TABLE IF EXISTS score_rank;
CREATE TABLE score_rank
(
    id        INT NOT NULL AUTO_INCREMENT,
    score_id  INT NOT NULL,
    rank      INT,
    last_rank INT,
    PRIMARY KEY (id),
);
SET @r=0;
INSERT INTO score_rank (last_rank,rank,score_id)
    SELECT B.rank,@r:= (@r+1),B.score_id FROM
    (SELECT id score_id FROM `score` ORDER BY points DESC LIMIT 10000) A
    LEFT JOIN `rank` B USING (score_id)
;
UPDATE rank A INNER JOIN score_rank B SET
    A.rank = B.rank,
    A.last_rank = B.last_rank
;
DROP TABLE IF EXISTS score_rank;

Mysql – How to optimise very simple query with subquery that only needs to execute once

Have you tried with a join?

SELECT a.products_id, a.products_model, a.products_iban_no
     , a.products_allocation_number
FROM products a
JOIN (
    SELECT products_model
    FROM products
    GROUP BY products_model
    HAVING COUNT(products_model) > 1
) as b
    on a.products_model = b.products_model

Best Answer

Related Solutions

Update Ranking on MySQL Table

Mysql – How to optimise very simple query with subquery that only needs to execute once

Related Question