List:General Discussion« Previous MessageNext Message »
From:Steve Staples Date:November 5 2010 12:25pm
Subject:Re: why mysql choose a bad query
View as plain text  
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

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