List:General Discussion« Previous MessageNext Message »
From:Todd Lyons Date:November 5 2010 7:21am
Subject:Re: why mysql choose a bad query
View as plain text  
2010/11/4 Changying Li <lchangying@stripped>:
>  PRIMARY KEY (`threadid`),
>  KEY `dateline` (`dateline`),
>  KEY `forumid_2` (`forumid`,`thread_type_id`,`visible`,`sticky`,`dateline`),
>  KEY `forumid` (`forumid`,`visible`,`sticky`,`dateline`)
> ) ENGINE=InnoDB AUTO_INCREMENT=660 DEFAULT CHARSET=utf8;
>
> mysql> explain  SELECT *  FROM `abc` WHERE `forumid` = 25 AND `visible`
> = 1 AND `sticky` = 0 order by dateline \G
> possible_keys: forumid_2,forumid
>          key: forumid_2
>        Extra: Using where; Using filesort
>
> why it choose forumid_2, not forumid ?

5.0 docs online say:

With EXPLAIN SELECT ... ORDER BY, you can check whether MySQL can use
indexes to resolve the query. It cannot if you see Using filesort in
the Extra column. See Section 7.2.1, “Optimizing Queries with
EXPLAIN”.

Your query is using filesort, so it cannot according to the above statement.

The docs also say in section 7.3.1.11 that it might not use an index if:

The key used to fetch the rows is not the same as the one used in the
ORDER BY...

I'm curious, if you change the SELECT to a few named fields instead of
*, does it affect the key choice?  If you only select on fields in the
key (i.e. a covering index) does it still choose what you consider to
be the wrong key?
-- 
Regards...      Todd
I seek the truth...it is only persistence in self-delusion and
ignorance that does harm.  -- Marcus Aurealius
Thread
why mysql choose a bad queryChangying Li5 Nov
  • Re: why mysql choose a bad queryTodd Lyons5 Nov
  • Re: why mysql choose a bad queryChangying Li5 Nov
    • Re: why mysql choose a bad querySteve Staples5 Nov