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 <tlyons@stripped> writes:
>
> > 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
>
> --
>
> 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
