From: Reindl Harald
Date: July 11 2012 9:46am
Subject: Re: why does "select * from table oder by indexed_field" not use
 key?
List-Archive: http://lists.mysql.com/mysql/227801
Message-Id: <4FFD4B68.3000000@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enigF31D40AEFD40794E6D0E4857"

--------------enigF31D40AEFD40794E6D0E4857
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 11.07.2012 11:43, schrieb Ewen Fortune:
> Hi,
>=20
> On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald <h.reindl@stripped=
> wrote:
>> the mysql query optimizer is somehow stupid
>=20
> Its not stupid - remember its not trying to find the best index,
> rather its trying to find the least costly plan
> to return the data in the quickest manner.
>=20
> For the optimizer in this case it believes its faster to do a full
> table scan with filesort rather than read from the index
> and have to scan the entire table anyway.
>=20
> Quick test shows it is indeed faster to do a full table scan.
>=20
> mysql> show profiles;
> +----------+------------+----------------------------------------------=
--------------------------------------------+
> | Query_ID | Duration   | Query
>                                             |
> +----------+------------+----------------------------------------------=
--------------------------------------------+
> |        1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
> cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
> |        2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
> cms1_quickbar_groups ORDER BY qg_sort ASC                     |
> +----------+------------+----------------------------------------------=
--------------------------------------------+
> 2 rows in set (0.00 sec)

this may be true for small data where it does not matter at all
but if this would be a large table it would cause a lot of I/O


--------------enigF31D40AEFD40794E6D0E4857
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk/9S2gACgkQhmBjz394AnlPoQCfTAExruBn/xrrkeYoxvV8D6Ia
42IAn2d+1wwOd+6wZzf6oc4l/c+LZU4t
=rYBL
-----END PGP SIGNATURE-----

--------------enigF31D40AEFD40794E6D0E4857--