7

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?

rlcabral
  • 357
  • 1
  • 3
  • 10

3 Answers3

3

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);
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • I was thinking on something like that. Limiting to 20,000 or 30,000 in my case it seems reasonable and show the rest as "ranked too low". Also, excluding from the table any record belonging to players that were not online in the last 6 months or cutting it off to keep only his/her top 10 records. – rlcabral Jun 08 '12 at 15:59
  • If I'm not mistaken this index: ALTER TABLE records ADD INDEX type_seconds_ndx (type,seconds DESC); would better because in where clause we have ORDER BY seconds DESC – Alireza Jun 09 '12 at 08:39
  • I get this warning when I run this: "warning(s): 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Please set variables in separate statements instead". Is there a better way? – user5071535 Oct 28 '18 at 22:37
3

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
Ragowit
  • 31
  • 1
0

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
vatavale
  • 101
  • 2