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.