On Fri, 2010-11-05 at 15:46 +0800, Changying Li wrote: > yes, I tried : > mysql> explain SELECT forumid,visible,sticky,dateline FROM `abc` WHERE > `forumid` = 25 AND `visible` = 1 AND `sticky` = 0 order by dateline \G > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: abc > type: ref > possible_keys: forumid_2,forumid > key: forumid > key_len: 8 > ref: const,const,const > rows: 24 > Extra: Using where; Using index > 1 row in set (0.00 sec) > > and tried : > mysql> explain select > threadid,thread_type_id,forumid,title,lastpost,open,replycount,postusername,postuserid,lastpostid,lastposter,lastpostuserid,dateline,views,visible,sticky,goodnees,votenum,votetotal,attach,hiddencount,deletedcount,pid > from abc where `forumid` = 25 AND `visible` = 1 AND `sticky` = 0 order by > dateline \G > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: abc > type: ref > possible_keys: forumid_2,forumid > key: forumid_2 > key_len: 3 > ref: const > rows: 24 > Extra: Using where; Using filesort > 1 row in set (0.00 sec) > > > but why ? > > Must I use it by force index ? > > > > Todd Lyons <tly...@ivenue.com> writes: > > > 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 > > -- > > Thanks & Regards > > Changying Li > >
what if you added the "dateline" into the where clause? i understand that the indexs are used left to right, but could it just be the order by is the issue? I dunno honestly, but it does apear that forumid_2 is a better choice based on the key_len and rows that the explain shows... Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
