Update ranking on table

I have two tables:

mysql> desc rank ;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| score_id       | int(11)     | NO   | MUL | NULL    |                |
| user_id        | int(11)     | NO   | MUL | NULL    |                |
| rank           | int(11)     | NO   | MUL | NULL    |                |
| last_rank      | int(11)     | NO   | MUL | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+

mysql> desc score ;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| points         | int(11)     | NO   | MUL | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+

What I need to do is once a day, update the 'rank' of all records.
Once a week, update the 'last_rank' of all records.

I can do a select that return a rank for each row:

SET @r = 0 ;

SELECT a.user_id, a.rank, @r:= (@r+1), a.last_rank,
                                   b.points
FROM rank AS a
JOIN score AS b
WHERE a.score_id = b.id
ORDER BY b.points ;

However, I cant find a way to UPDATE the Rank table with this result...

• How can I do this?

• Is there a best (fast) way to do this?

MySQL Version:

mysql> SELECT VERSION();

+------------+
| VERSION()  |
+------------+
| 5.1.61-log |
+------------+

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;

StackExchange Database Administrators Q#29007, answer score: 2