CODEWITHSUNDEEP
mysqlsql
Riya
Riya

Reputation: 15

Leaderboard position SQL optimization

I'm offering an experience leaderboard for a Discord bot I actively develop with stuff like profile cards showing one's rank. The SQL query I'm currently using works flawlessly, however I notice that this query takes a rather long processing time. 

    SELECT id, 
           discord_id, 
           discord_tag, 
           xp, 
           level 
    FROM   (SELECT @rank := @rank + 1 AS id, 
                   discord_id, 
                   discord_tag, 
                   xp, 
                   level 
            FROM   profile_xp, 
                   (SELECT @rank := 0) r 
            ORDER  BY xp DESC) t 
    WHERE  discord_id = '12345678901';

The table isn't too big (roughly 20k unique records), but this query is taking anywhere between 300-450ms on average, which piles up relatively fast with a lot of concurrent requests.

I was wondering if this query can be optimized to increase performance. I've isolated this to this query, the rest of the MySQL server is responsive and swift.

I'd be happy about any hint and thanks in advance! :)

Upvotes: 1

Views: 508

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272406

You're scanning 20,000 rows to assign "row numbers" then selecting exactly one row from it. You can use aggregation instead:

SELECT *, (
    SELECT COUNT(*)
    FROM profile_xp AS x
    WHERE xp > profile_xp.xp
) + 1 AS rnk
FROM profile_xp
WHERE discord_id = '12345678901'

This will give you rank of the player. For dense rank use COUNT(DISTINCT xp). Create an index on xp column if necessary.

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15961

Assuming discord_id is the primary key for the table, and you're just trying to get one entry's "rank", you should be able to take a different approach.

SELECT px.discord_id, px.discord_tag, px.xp, px.level
   , 1 + COUNT(leaders.xp) AS rank
   , 1 + COUNT(DISTINCT leaders.xp) AS altRank
FROM profile_xp AS px
LEFT JOIN profile_xp AS leaders ON px.xp < leaders.xp
WHERE px.discord_id = '12345678901'
GROUP BY px.discord_id, px.discord_tag, px.xp, px.level
;

Note I have "rank" and "altRank". rank should give you a similar position to what you were originally looking for; your results could have fluctuated for "ties", this rank will always put tied players at their highest "tie". If 3 records tie for 2nd place, those (queried separately with this) will show 2nd place, the next xp down would should 5th place (assuming 1 in 1st, 2,3,4 in 2nd, 5 in 5th). The altRank would "close the gaps" putting 5 in the 3rd place "group".

I would also recommend an index on xp to speed this up further.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

Not an answer; too long for a comment:

I usually write this kind of thing exactly the same way that you have done, because it's quick and easy, but actually there's a technical flaw with this method - although it only becomes apparent in certain situations.

By way of illustration, consider the following:

DROP TABLE IF EXISTS ints;

CREATE TABLE ints (i INT NOT NULL PRIMARY KEY);

INSERT INTO ints VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

Your query:

 SELECT a.*
      , @i:=@i+1 rank
   FROM ints a
   JOIN (SELECT @i:=0) vars
  ORDER
     BY RAND() DESC;
    +---+------+
    | i | rank |
    +---+------+
    | 3 |    4 |
    | 2 |    3 |
    | 5 |    6 |
    | 1 |    2 |
    | 7 |    8 |
    | 9 |   10 |
    | 4 |    5 |
    | 6 |    7 |
    | 8 |    9 |
    | 0 |    1 |
    +---+------+

Look, the result set isn't 'random' at all. rank always corresponds to i

Now compare that with the following:

SELECT a.*
     , @i:=@i+1 rank 
  FROM 
     ( SELECT * FROM ints ORDER by RAND() DESC) a 
  JOIN (SELECT @i:=0) vars;
+---+------+
| i | rank |
+---+------+
| 5 |    1 |
| 2 |    2 |
| 8 |    3 |
| 7 |    4 |
| 4 |    5 |
| 6 |    6 |
| 0 |    7 |
| 1 |    8 |
| 3 |    9 |
| 9 |   10 |
+---+------+

Upvotes: 1

Related Questions