Query using "ref" instead of "range" access

[Keldon Jones]

Hi,

I have a table of player's scores on various game levels. The table is fairly large (220 million rows):

CREATE TABLE `scores` (
  `lid` smallint(5) unsigned NOT NULL,
  `pid` mediumint(8) unsigned NOT NULL,
  `score` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`lid`,`pid`),
  KEY `rank_index` (`lid`,`score`),
  KEY `player_index` (`pid`,`lid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

My problem query is where I attempt to look at a player's score for each level and also compute their ranking among other players of the level:

SELECT SQL_NO_CACHE scores.lid, scores.score, COUNT(s2.score)+1
FROM scores
JOIN scores AS s2
     ON s2.lid=scores.lid
     AND s2.score>scores.score
WHERE scores.pid=186359
GROUP BY scores.lid;

This takes about 7 seconds to run. EXPLAIN shows:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: scores
         type: ref
possible_keys: PRIMARY,rank_index,player_index
          key: player_index
      key_len: 3
          ref: const
         rows: 928
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
         type: ref
possible_keys: PRIMARY,rank_index
          key: rank_index
      key_len: 2
          ref: test_db.scores.lid
         rows: 16319
        Extra: Using where; Using index

My problem is that the access of table s2 is using ref, not range. If I do a ranking query for a single level:

SELECT COUNT(score)+1
FROM scores
WHERE lid=11801
AND score>55454;

then I do get range access using the full width of the rank_index:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: scores
         type: range
possible_keys: PRIMARY,rank_index
          key: rank_index
      key_len: 5
          ref: NULL
         rows: 4331
        Extra: Using where; Using index

I have a sort of workaround:

CREATE FUNCTION score_rank (lid smallint unsigned, score mediumint unsigned)
RETURNS int
BEGIN
    DECLARE rank int;
    SELECT COUNT(scores.score)+1 INTO rank
       FROM scores
       WHERE scores.lid=lid
       AND scores.score>score;
    RETURN rank;
END

and changing my original query to:

SELECT SQL_NO_CACHE scores.lid, scores.score,
       score_rank(scores.lid, scores.score)
FROM scores
WHERE scores.pid=186359;

This takes about 2 seconds, but I can't see exactly why (EXPLAIN doesn't show me the subquery inside the score_rank function). I assume that it is using range access.

Is there a better way to do this with a JOIN instead of a stored function? I like the transparency of the join better.