Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
+------------+
share|improve this question
    
google 'update statement mysql' ? –  Max Vernon Nov 20 '12 at 18:40
    
Max Vernon, with join and order? cant find any solution –  fabriciols Nov 20 '12 at 18:46
    
Also, 'rank' is used in analytical SQL statements and as a result it is bad practice to name a table so. –  Phil Nov 20 '12 at 18:47
    
What version of MySQL? (run SELECT VERSION();. Also, please run SHOW CREATE TABLE score\G and SHOW CREATE TABLE rank\G and post it in the question. –  RolandoMySQLDBA Nov 20 '12 at 20:03
    
added mysql version... these tables is just one example that thought in my head –  fabriciols Nov 20 '12 at 20:10
add comment

1 Answer

up vote 1 down vote accepted

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;
share|improve this answer
    
I got "ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY" –  fabriciols Nov 20 '12 at 19:01
    
I gotta run to a company meeting, Back back in an hour... (I am still thinking..., error is probably ORDER BY two JOINed tables in UPDATE. My past answer did ORDER BY on a single table) –  RolandoMySQLDBA Nov 20 '12 at 19:04
    
I think ORDER BY...DESC cannot be applied on a JOIN. I changed my answer to perform ORDER BY...DESC on score table before doing the JOIN. –  RolandoMySQLDBA Nov 20 '12 at 20:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.