From: Rick James
Date: July 16 2012 9:57pm
Subject: RE: Subquery taking too much time on 5.5.18?
List-Archive: http://lists.mysql.com/mysql/227833
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148892B763@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

> query B can not used any key because 'like' never can use any key
Not true.  LIKE without a leading wildcard is optimized like a BETWEEN.

> -----Original Message-----
> From: Reindl Harald [mailto:h.reindl@stripped]
> Sent: Friday, July 06, 2012 8:58 AM
> To: mysql@stripped
> Subject: Re: Subquery taking too much time on 5.5.18?
>=20
>=20
>=20
> Am 06.07.2012 17:46, schrieb Cabbar Duzayak:
> > Hi Everyone,
> >
> > 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.
> >
> > In a nutshell, I have 2 tables: A and B. And, I do something like
> this:
> >
> > SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like
> > 'X%');
> >
> > 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.
>=20
> query B can not used any key because 'like' never can use any key
>=20
> 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)
>=20
> such things i would always do with two queries in the application
>=20
> * first the sub-query
> * genearte the query above with the results in the app
> * fire up the final query