From: Reindl Harald
Date: July 6 2012 3:58pm
Subject: Re: Subquery taking too much time on 5.5.18?
List-Archive: http://lists.mysql.com/mysql/227779
Message-Id: <4FF70B11.5060909@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enig7C83A9ABD1155F0B64A6FF3C"

--------------enig7C83A9ABD1155F0B64A6FF3C
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable



Am 06.07.2012 17:46, schrieb Cabbar Duzayak:
> Hi Everyone,
>=20
> I have been trying to understand why subqueries are taking tooo much
> time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release.
>=20
> In a nutshell, I have 2 tables: A and B. And, I do something like this:=

>=20
> SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like 'X%=
');
>=20
> Table A has 460,000 rows and Table B has 5000 rows, and A.id is my
> primary key, and B.name is indexed. Also, the sub-query here (B.name
> starts with X%) returns about 300 rows.

query B can not used any key because 'like' never can use any key

i try to avoid subqueries wherever i can becasue the mysql
query-optmizer is really weak in most cases (it appears
5.6 will be much better in many of them)

such things i would always do with two queries in the application

* first the sub-query
* genearte the query above with the results in the app
* fire up the final query


--------------enig7C83A9ABD1155F0B64A6FF3C
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/3CxEACgkQhmBjz394Annr7wCfYifWgBKToLFhsGX7EN/hG0zM
YXkAmwd4igU5kzLmneZ0dDkllKpwibIv
=WtFK
-----END PGP SIGNATURE-----

--------------enig7C83A9ABD1155F0B64A6FF3C--