Ranking of a user in mysql

Question

I know this has been asked and solved in the past but I couldn't exactly apply the already solved problems to mine.

What I am trying to do:

Get the global ranking of a user along with the 10 below and 10 above them and the top rankings for the top 20 users (ELO).

I have managed to get the top 20 users, but I am having issues getting a single user's ranking along with the 10 above and 10 below them in a single fast query. In addition, I am having doubts as to how fast using Rank over() is in the event that I have over 1M rows. Lastly, no players should have the same ranking even if their ELO is the same (hence the multiple order by).

Below is my table

CREATE TABLE IF NOT EXISTS elo_ladder
(
    elo_ladder_incr INT NOT NULL AUTO_INCREMENT,
    player_id INT DEFAULT NULL,
    
    elo_rank INT DEFAULT 1000,
    elo_rank_wins INT DEFAULT 0,
    
    PRIMARY KEY (elo_ladder_incr),
    KEY elo_rank_key (elo_rank),
    KEY elo_rank_wins_key (elo_rank_wins),
    FOREIGN KEY (player_id) REFERENCES users(player_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

Now the query below is the one I have for getting the top 20 users.

// Get top ranked players

  SELECT
    player_id,
    elo_rank,
    elo_rank_wins
  FROM elo_ladder
  ORDER BY 
  elo_rank DESC, 
  elo_rank_wins DESC
  LIMIT 20;

// Get single player's ranking

SELECT
    *
FROM (
    SELECT
      player_id,
      t.elo_rank,
      (RANK() OVER (ORDER BY t.elo_rank DESC, t.elo_rank_wins DESC)) AS global_rank
    FROM (
      SELECT
        player_id,
        elo_rank,
        elo_rank_wins
      FROM elo_ladder
      GROUP BY player_id
    ) AS t
) AS rt
WHERE rt.player_id = 30;

Lastly, I am using Mysql InnoDB.

score 0 · Answer 1 · answered Mar 17 '21 at 06:09

Get everyone's RANK. This will be used in a derived table (FROM ( SELECT ... ))
Build around it a SELECT with a where clause with rank between user_rank - 10 and user_rank + 10.

It may be better to use ROW_NUMBER than RANK, especially if you deal with RANK versus DENSE_RANK.

You may need to use WITH or a temp table and it may be beneficial to add an index.

But it is likely that dealing with "rank" over a million rows is destined to be slow.

Step 2, above, may be replaced by

( SELECT ... WHERE rank < user_rank ORDER BY rank DESC LIMIT 10 )
UNION ALL
( SELECT ... WHERE rank > user_rank ORDER BY rank ASC  LIMIT 10 )

At that point, you don't need the actual "rank", you can simply use the metric that controls the rank.

Good luck.

Ranking of a user in mysql

1 Answers1