MySQL query to update players weekly ranking positions with large dataset [closed]

I’m trying to update weekly ranking scores for players, but any query I have tried just runs to timeout. There are around 100k rows in the table. My table players_weekly_rankings looks like this:

player_id | ranking_points | yearweek | ranking_pos
22        | 1676           | 2020/01  | 1
12        | 1620           | 2020/01  | 2
45        | 1620           | 2020/01  | 2
53        | 1544           | 2020/01  | 4
25        | 1644           | 2020/02  | 1
21        | 1555           | 2020/02  | 2
etc.

So the ranking_pos column is the one being updated.

And the query that never finishes and runs to timeout:

update players_weekly_ranking
set ranking_pos = (
    select count(distinct ranking_points) + 1
    from (SELECT ranking_points, yearweek FROM players_weekly_ranking) w2
    where w2.yearweek = players_weekly_ranking.yearweek and w2.ranking_points > players_weekly_ranking.ranking_points
)

And the EXPLAIN as requested below (this test tebale has only 2000 records, but actual table near 100k)

With upto couple of thousand rows, it finishes in two minutes, but anything above that, timeout is reached.

Is there more optimized way to do this, so the query wouldn’t run to timeout? Thanks!

Answer

I believe this should do the trick in a single pass (no subqueries)

SET @rank = 0;

UPDATE players_weekly_ranking
SET ranking_pos = (@rank := @rank+1)
WHERE yearweek = '2020/01'
ORDER BY ranking_points DESC;

It defines a variable @rank starting at 0, then iterates over all rows for a specific yearweek, by descending ranking_points and assigns their ranking_pos. (@rank := @rank+1) is there to increment the variable at each row.

Edit: I’m assuming you only need to update the ranking for a specific week, since scores in the past should not change

Edit2: Here is a version that takes into account equal points and can update several yearweeks:

SET @rank = 0; -- rank of the previous row
SET @yearweek = ''; -- yearweek of the previous row
SET @last_score = 0; -- score of the previous row
SET @nb_same_score = 0; -- number of rows "in a row" with same score

UPDATE players_weekly_ranking
SET ranking_pos = IF(
        @yearweek != (@yearweek := yearweek), 
        IF( -- if it's a new yearweek
            (@last_score := ranking_points) AND (@nb_same_score:=1),
            (@rank := 1), -- first row always gets ranking_pos = 1
            0
        ), 
        IF( -- if same yearweek
            @last_score = (@last_score := ranking_points) AND (@nb_same_score := @nb_same_score + 1),
            @rank, -- if same score as last row => set same ranking_pos
            @rank := @rank + @nb_same_score + (@nb_same_score := 1) -1
        )
    )
ORDER BY yearweek, ranking_points DESC;

Iterating over each rows, ordered by yearweek and points, this does the following:

• if its a new week, the rank for the first row (highest score) is always 1. (@yearweek takes the value of the new week, @last_score & @nb_same_score are reset)
• if it’s the same week as last row, @last_score is compared with the row’s score (and updated). If they are equal, @nb_same_score is incremented
  • If equal, the row gets the same rank as the previous one
  • otherwise it gets + @nb_same_score to its rank. ((@nb_same_score := 1) -1 is just there to reset the @nb_same_score variable to 1)