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.
-----Original Message-----
From: Reindl Harald [mailto:[email protected]]
Sent: Friday, July 06, 2012 11:58 AM
To: [email protected]
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
> '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.
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
