0

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) enter image description here

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!

user3112031
  • 39
  • 7

2 Answers2

0

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)
Pepper
  • 587
  • 4
  • 12
  • Actually I need to update rankings to the part for at least one year, so I think this one wouldn't work. And I think this doesn't take into consideration the equal rankings with players with same score. – user3112031 Nov 09 '20 at 10:37
  • You are correct, it's not taking into account same scores :< I'll update it in a bit! – Pepper Nov 09 '20 at 10:38
  • @user3112031 I added a new version under **edit2**. It should work for several yearweek, and handles players with same score – Pepper Nov 09 '20 at 11:09
  • 1
    Excellent! Your last query works like a charm and finishes in just a few seconds. Thank you so much! – user3112031 Nov 09 '20 at 12:00
0
  1. Replace sub-query with JOIN
  2. In the JOIN'ed table calculate the rank using window-function RANK(). It will do exactly what you need.

RANK() is available only in MySQL 8+ https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_rank

If you have MySQL 5.* look for workarounds without RANK() function: Rank function in MySQL

Stalinko
  • 3,319
  • 28
  • 31