Update rank on a large table

Question

I have a mysql table that has now 12 million records and I'm trying to update a column (rank) with the following UPDATE code:

SET @r=0;
UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY seconds DESC;

Almost every minute new rows are inserted. So, after one hour, I have several rows not ranked and I need to update the whole table again.

However, it takes too much time. Is there any way to optimize or a different way to update the table faster? Maybe a different approach? Partitioning the table?

Answer 1

Rhetorical Question: Do you really need to rank all 12,000,000 records?

At my employer's web hosting company, we have a gaming client that ranks its top 10,000 players per game platform. They use the same construct you do. You should just limit it to the top 10,000 or whatever number is reasonable

SET @r=0;
UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY seconds DESC LIMIT 10000;

I would run your original query at midnight to get them all

If you must try to rank all, maybe you can try something like this:

DROP TABLE IF EXISTS ranktable;
CREATE TABLE ranktable
(
    rank INT NOT NULL AUTO_INCREMENT,
    rec_id INT NOT NULL,
    PRIMARY KEY (rank)
);
INSERT INTO ranktable (rec_id)
SELECT rec_id FROM records WHERE type=2 ORDER BY seconds DESC;
UPDATE records A INNER JOIN ranktable B USING (rec_id) SET A.rank = B.rank;

What may further help is to index your records table like this:

ALTER TABLE records ADD INDEX type_seconds_ndx (type,seconds);

Answer 2

If you are using MySQL 8 so can you use the new function RANK()

SELECT
    id,
    RANK() OVER (
       ORDER BY seconds DESC
    ) ranking
FROM
    records
WHERE
    type = 2;

Depending on how you want to display the ranking with even score so can you also check out DENSE_RANK()

And as an UPDATE:

WITH
   ranking AS(
   SELECT
      id,
      RANK() OVER (
         ORDER BY seconds DESC
      ) ranking
    FROM
        records
    WHERE
        type = 2
)
UPDATE
    records r1,
    ranking r2
SET
    r1.rank = r2.ranking
WHERE
    r1.id = r2.id

Answer 3

Update with RANK() and JOIN syntax:

UPDATE records

  JOIN (SELECT id,
               RANK() OVER (ORDER BY seconds DESC) rank
          FROM records
         WHERE type = 2
       ) AS ranks ON ranks.id = records.id

   SET records.rank = ranks.rank