2010/11/4 Changying Li <lchangy...@gmail.com>: > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
