Update rank on a large table

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?

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);

StackExchange Database Administrators Q#18987, answer score: 3