As far as I know, a B-tree index can be used by LIKE as long as the string doesn't begin
with a wildcard. " LIKE 'X%' " should be fine to use an index on the name column. The
index only includes results in the search base which start with X.
That said, I probably wouldn't use a subquery, either. But since the OP says they'd prefer
to use subqueries, try this and tell me what happens:
SELECT * FROM A WHERE A.id IN (
SELECT A_ID FROM (
SELECT A_ID FROM B WHERE B.name LIKE 'X%'
) AS x
It's just wrapping the subquery within another subquery, forcing MySQL to run from the
inside out. I don't have a running instance nearby to test on, but I hope it helps.
From: Reindl Harald [mailto:h.reindl@stripped]
Sent: Friday, July 06, 2012 11:58 AM
Subject: Re: Subquery taking too much time on 5.5.18?
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
> 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
Notice: This communication may contain privileged and/or confidential information. If you
are not the intended recipient, please notify the sender by email, and immediately delete
the message and any attachments without copying or disclosing them. LBI may, for any
reason, intercept, access, use, and disclose any information that is communicated by or
through, or which is stored on, its networks, applications, services, and devices.