mysql - My SQL Command takes too long, any simplification? -

i have table (player) of following columns:

playerid varchar(20) mmr int(10) rank int(10) 

there 500k~ rows @ moment.

now want update rank column of top 10k players.

so created following sql command

start transaction;  update player  set player.rank = 10001  player.rank < 10001;  set @rank := 0;  create temporary table if not exists tmprank (select @rank := @rank + 1 rank, playerid, mmr    player order mmr desc limit 10000);   update player, tmprank  set player.rank = tmprank.rank  player.playerid = tmprank.playerid;  commit; 

sadly command takes 25 seconds complete, set indices on playerid, mmr, , rank.

can me simplify command or suggest how improve piece of code?

best regards

edit #1

table player:

column                      typ         null    standard ----------------------------------------------------------- playerid (primärschlüssel)  varchar(20) nein              name                        varchar(16) nein              password                    varchar(10) nein              mmr                         int(10)     nein    1500          rank                        int(10)     nein    50001         tournaments_won             int(10)     nein    0         avatar                      varchar(255)nein              location                    tinyint(4)  nein    0         playerlevel                 tinyint(4)  nein    0         experience                  tinyint(4)  nein    0         description                 varchar(255)nein    hello people!         

indices

schlüsselname   typ     unique  gepackt spalte      kardinalität    kollation   null     primary         btree   ja      nein    playerid    479032            nein     deviceid        btree   ja      nein    deviceid    479032            nein     name            btree   ja      nein    name        479032            nein     mmr             btree   nein    nein    mmr         9580              nein     rank            btree   nein    nein    rank        2                 nein 

how sql query "should work":

• "derank" every player setting rank internal max+1 (only set players ranked before)

• create temporary table top 10k players

• update player table in comparison temp table , change ranks if necessary

edit #2

update player  set player.rank = 10001  player.rank < 10001; 

takes 20 seconds

set @rank := 0; create temporary table if not exists tmprank (select @rank := @rank + 1 rank, playerid, mmr    player order mmr desc limit 10000); 

this query alone takes <0.5 seconds

set @rank := 0; create temporary table if not exists tmprank (select @rank := @rank + 1 rank, playerid, mmr    player order mmr desc limit 10000);   update player, tmprank set player.rank = tmprank.rank player.playerid = tmprank.playerid; 

these 2 queries taking 20 seconds again.

i want run them every 30min or 1hour, prefer if run lot faster somehow.

the query heavy duty.

will doing update often? if so, contend poor design have massively updated column in table. instead have in ("parallel") table.

doing so, speed query. may practical create table id+rank, rename table put place. have 0 'downtime'. yes, still takes long time compute , build, users not impacted. assume real concern.

if possible, compute value on fly rather storing it. (i doubt if practical, since call "rank".)