List:General Discussion« Previous MessageNext Message »
From:Changying Li Date:November 5 2010 7:46am
Subject:Re: why mysql choose a bad query
View as plain text  
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

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