Skip to content
Advertisement

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:

So the ranking_pos column is the one being updated.

And the query that never finishes and runs to timeout:

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!

Advertisement

Answer

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

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:

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)
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement