In our games we have a quartely league system. Every 3 months, the current season ends and all scorings of the current quarter are archived into a table called archive_league. Each entry represents a user and his score for a given league quarter. It comes with the following fields:
id - the id of an entry uid - the id of a user rounds - the number of games the user played in the given quarter score - the score of the user for the given quarter rank - the position of the player, descending by score, in the given quarter date - the date of the quarter this entry represents
My goal is to go through all entries for a given date and assign the field "rank" for results. For example the player with the highest score should receive rank = 1, 2nd highest score rank = 2 and so on. If 2 players share the same score, they should receive the same rank (olympic scoring).
player x with score 528 receives rank 7 player y with score 528 receives rank 7 player z with score 529 receives rank 9
I am currently achieving this goal with this query:
UPDATE archive_league LEFT JOIN (SELECT t.id, (SELECT COUNT(id) + 1 FROM archive_league x WHERE x.score > t.score AND x.date = :quarter) AS new_rank FROM archive_league t) AS temp USING (id) SET rank = new_rank WHERE date = :quarter;
The problem is, however, with some hundreds of thousands of entries, this query runs for a couple of days, even though I have created indexes for the WHERE conditions. How can I optimize this query to run faster?