Cabbar, try to replace the IN subquery with an EXISTS. Something like:
SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and B.name like 'X%');
Does it help?
From: Cabbar Duzayak [mailto:cabbar@stripped]
Sent: Friday, July 06, 2012 11:46 AM
Subject: Subquery taking too much time on 5.5.18?
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.
For some weird reason, this query takes a ton of time (I cancelled it
after 750 seconds). I looked at the query plan with EXPLAIN and it
could not find an index to use for table A and looks like it is doing
a table scan (even though A.id is the primary key)...
To understand it better, I divided it up, and sent two queries
separately as follows::
"SELECT A_ID FROM B WHERE B.name like 'X%'"
takes 0.002 second.
For testing purposes, I concatenated all ids from this query and send
a hard-coded query on A like:
SELECT * FROM A WHERE A.id in (1,2,3,4,5.....)
and this takes 0.002 second.
Basically, both queries are super fast, but when I combine them via IN
w/sub-query, the thing spends a lot more time?
As an alternative, I tried using JOIN as follows:
SELECT A.* FROM A INNER JOIN B ON (A.id = B.A_id) WHERE B.name like 'X%';
and this takes 0.04 seconds
JOIN is also fast, but there are cases where I really need IN subqueries.
I would really really appreciate it if you can shed some light on this
issue and tell me what I am doing wrong and/or how I can fix this?
Thanks a ton.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
The information contained in this e-mail and any attached
documents may be privileged, confidential and protected from
disclosure. If you are not the intended recipient you may not
read, copy, distribute or use this information. If you have
received this communication in error, please notify the sender
immediately by replying to this message and then delete it
from your system.