From: Todd Lyons
Date: November 5 2010 7:21am
Subject: Re: why mysql choose a bad query
List-Archive: http://lists.mysql.com/mysql/223512
Message-Id: <AANLkTimCEMPtEbRLSHT87mg6t4JQJkpPK_UkYEAb+FoP@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

2010/11/4 Changying Li <lchangying@stripped>:
> =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