MySQL Forums
Forum List  »  Optimizer & Parser

Query using "ref" instead of "range" access
Posted by: Keldon Jones
Date: July 09, 2009 04:52PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Query using "ref" instead of "range" access
3418
July 09, 2009 04:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.