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 <[email protected]> writes:
> 2010/11/4 Changying Li <[email protected]>:
>> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
