why mysql choose a bad query

CREATE TABLE `abc` (
`threadid` int(11) NOT NULL AUTO_INCREMENT COMMENT '主题id',
`thread_type_id` int(11) DEFAULT NULL COMMENT '主题类别表id',
`forumid` smallint(6) DEFAULT NULL COMMENT '主键id',
`title` varchar(250) DEFAULT NULL COMMENT '标题',
`lastpost` int(11) DEFAULT NULL COMMENT '最后回复时间',
`open` smallint(6) DEFAULT NULL COMMENT '是否开放，0为不开放,1为开放,10为被移动的',
`replycount` int(11) DEFAULT NULL COMMENT '回帖总数',
`postusername` varchar(100) DEFAULT NULL COMMENT '发表人名称',
`postuserid` int(11) DEFAULT NULL COMMENT '发表者id',
`lastpostid` int(11) DEFAULT NULL,
`lastposter` varchar(100) DEFAULT NULL COMMENT '最后回复人',
`lastpostuserid` int(11) DEFAULT NULL,
`dateline` int(11) DEFAULT NULL COMMENT '信息发表时间',
`views` int(11) DEFAULT NULL COMMENT '查看次数',
`visible` smallint(6) DEFAULT NULL COMMENT '是否有效,0为未审核,1为审核,2为软删除,3为草 稿箱',
`sticky` tinyint(1) DEFAULT NULL COMMENT '是否置顶',
`goodnees` tinyint(1) DEFAULT NULL COMMENT '1为精华,0为非精华,默认为0',
`votenum` smallint(6) DEFAULT NULL COMMENT '投票个数',
`votetotal` int(11) DEFAULT NULL COMMENT '投票总分',
`attach` smallint(6) DEFAULT NULL COMMENT '附件个数',
`hiddencount` int(11) DEFAULT NULL COMMENT '未审核文章总数',
`deletedcount` smallint(6) DEFAULT NULL COMMENT '被删除的回复总数',
`pid` int(11) DEFAULT NULL COMMENT '当open不为10的时候它代表投票id,当open等于10æ— ¶åˆ™ä¸ºè¢«ç§»åŠ¨çš„帖子的id',
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
*************************** 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)

why it choose forumid_2, not forumid ?


--

Thanks & Regards

Changying Li


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: why mysql choose a bad query

2010/11/4 Changying Li :
> =A0PRIMARY KEY (`threadid`),
> =A0KEY `dateline` (`dateline`),
> =A0KEY `forumid_2` (`forumid`,`thread_type_id`,`visible`,`sticky`,`dateli=
ne`),
> =A0KEY `forumid` (`forumid`,`visible`,`sticky`,`dateline`)
> ) ENGINE=3DInnoDB AUTO_INCREMENT=3D660 DEFAULT CHARSET=3Dutf8;
>
> mysql> explain =A0SELECT * =A0FROM `abc` WHERE `forumid` =3D 25 AND `visi=
ble` =3D 1 AND `sticky` =3D 0 order by dateline \G
> possible_keys: forumid_2,forumid
> =A0 =A0 =A0 =A0 =A0key: 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, =93Optimizing Queries with
EXPLAIN=94.

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?
--=20
Regards...=A0 =A0 =A0 Todd
I seek the truth...it is only persistence in self-delusion and
ignorance that does harm.=A0 -- Marcus Aurealius

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: why mysql choose a bad query

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,replycou nt,postusername,postuserid,lastpostid,lastposter,lastpostuse rid,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 writes:

> 2010/11/4 Changying Li :
>>  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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: why mysql choose a bad query

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` =3D 25 AND `visible` =3D 1 AND `sticky` =3D 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)
>=20
> and tried :
> mysql> explain select threadid,thread_type_id,forumid,title,lastpost,open=
,replycount,postusername,postuserid,lastpostid,lastposter,la stpostuserid,da=
teline,views,visible,sticky,goodnees,votenum,votetotal,attac h,hiddencount,d=
eletedcount,pid from abc where `forumid` =3D 25 AND `visible` =3D 1 AND `s=
ticky` =3D 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)
>=20
>=20
> but why ?
>=20
> Must I use it by force index ?
>=20
>=20
>=20
> Todd Lyons writes:
>=20
> > 2010/11/4 Changying Li :
> >> PRIMARY KEY (`threadid`),
> >> KEY `dateline` (`dateline`),
> >> KEY `forumid_2` (`forumid`,`thread_type_id`,`visible`,`sticky`,`datel=
ine`),
> >> KEY `forumid` (`forumid`,`visible`,`sticky`,`dateline`)
> >> ) ENGINE=3DInnoDB AUTO_INCREMENT=3D660 DEFAULT CHARSET=3Dutf8;
> >>
> >> mysql> explain SELECT * FROM `abc` WHERE `forumid` =3D 25 AND `visib=
le` =3D 1 AND `sticky` =3D 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, â€=9COptimizing Queries with
> > EXPLAINâ€=9D.
> >
> > Your query is using filesort, so it cannot according to the above state=
ment.
> >
> > 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?
> > --=20
> > Regards... Todd
> > I seek the truth...it is only persistence in self-delusion and
> > ignorance that does harm. -- Marcus Aurealius
>=20
> --=20
>=20
> Thanks & Regards
>=20
> Changying Li
>=20
>=20

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=3Dgcdmg-mysql-2@m.gmane.o rg