Optimise rank data

from the Artful MySQL Tips List

Baron Schwartz has a useful page on optimising rankings for leaderboards. It requires maintenance of a ranking computation table, as described on his page under "How to maintain the rank column". That table has this structure:

CREATE TABLE score_ranked (
  gamer INT NOT NULL,
  game INT NOT NULL,
  score INT NOT NULL,
  rank_in_game INT NOT NULL DEFAULT 0,
  PRIMARY KEY (gamer,game),
  KEY (game,score),
  KEY (score),
  KEY (game,rank_in_game)
) ENGINE=InnoDB;

If you use Baron's schema, you will find the following query, developed on a MySQL forum by Tom Grant and Rick James, efficient for retrieving top score sums for all games---it finds the top ten of 100k rows in 0.2 secs:

SELECT 
  @top := 10,         -- desired top number including ties
  @seq := 0,          -- raw rank 1,2,3,...
  @rank := 0,         -- rank incorporating ties
  @prev := 999999999; -- placeholder for ties
SELECT Rank, gamer, points
FROM (
  SELECT 
    @seq := @seq + 1 AS seq,
    @rank := IF(@prev = points, @rank, @seq) AS 'Rank',
    @prev := points as prev, 
    gamer,
    points
  FROM (
    SELECT gamer, SUM(score) as points
    FROM score_ranked s
    GROUP BY gamer
    ORDER BY points DESC
  ) x
) y
WHERE `Rank` <= @top
ORDER BY `Rank`, gamer;

Last updated 25 Aug 2017

Return to the Artful MySQL Tips page