CODEWITHSUNDEEP
mysqlsqlperformancesqlperformance
Vicenç Gascó
Vicenç Gascó

Reputation: 1346

UPDATE with a SELECT to create a ranking (effiency)

I have a ranking that every X time has a balance added to the total points of each player, changing how they are ranked. I want my rank to be calculated at DB (MySQL), but I want it to be efficient, so first things first, here's the BalanceIn code:

Q1:

UPDATE     playerscore
SET        points = points * 0.9 + GREATEST(-100, LEAST(100, balance)),
           balance = 0;

Once the points are updated for all them, I want to reorder the rank (only for ranked players), like so:

Q2: 

SET        @r = 0;
UPDATE     playerscore p
INNER JOIN 
           (SELECT   @r:=@r+1 as new_rank, player_id
            FROM     playerscore
            WHERE    is_ranked = 1
            ORDER BY points DESC) s
ON         p.player_id = s.player_id
SET        p.rank = s.new_rank
WHERE      is_ranked = 1;

It works, and solves my problem, but: is thisgoing to make 1 select, and from here update all values, or will it make a select for each playerscore row?

In pseudocode, that's what I DO NOT want:

foreach PlayerScore p in playescore
    new = get_all_players_sorted
    update p.rank = new.new_rank where p.player_id = new.player_id
endforeach

As it will be, for N players: N selects + N updates. I want it to be: 1 select + N updates (contained in a single update) like so:

new = get_all_players_sorted
foreach PlayerScore p in playescore
    update p.rank = new.new_rank where p.player_id = new.player_id
endforeach

Am I doing it right with my query (Q2)??

Upvotes: 1

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If this is your query, you can perhaps speed it up by rephrasing the query and using an index.

The index you want is playerscore(is_ranked, points desc). The query is:

SET @r = 0;

UPDATE playerscore p
    SET p.rank = (@r := @r + 1)
    WHERE is_ranked = 1
    ORDER BY points desc;

In MySQL, you can use order by with update -- as long as you are not using a join. In this case, you don't need a join because you are setting the variable as a separate statement.

Upvotes: 1

Related Questions