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 |
+------------+
Best Answer
I address this question back on Jun 08, 2012 : Update rank on a large table
First, make sure you have an index on
rank
andlast_rank
in therank
tableNext, make sure you have a
points
index on the score tableIf you do not have such indexes, run the following:
You are going to have to update the last_rank on the fly using an
UPDATE...JOIN
:Here is a more paranoid version
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
or this