List:General Discussion« Previous MessageNext Message »
From:David Lerer Date:July 6 2012 10:07pm
Subject:RE: Subquery taking too much time on 5.5.18?
View as plain text  
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?

David.

-----Original Message-----
From: Cabbar Duzayak [mailto:cabbar@stripped] 
Sent: Friday, July 06, 2012 11:46 AM
To: mysql@stripped
Subject: Subquery taking too much time on 5.5.18?

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.

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.
Thread
Subquery taking too much time on 5.5.18?Cabbar Duzayak6 Jul
  • Re: Subquery taking too much time on 5.5.18?Reindl Harald6 Jul
    • RE: Subquery taking too much time on 5.5.18?Benjamin Stillman6 Jul
      • Re: Subquery taking too much time on 5.5.18?Cabbar Duzayak6 Jul
    • RE: Subquery taking too much time on 5.5.18?Rick James16 Jul
  • RE: Subquery taking too much time on 5.5.18?David Lerer6 Jul
    • Re: Subquery taking too much time on 5.5.18?Peter Brawley7 Jul
      • Re: Subquery taking too much time on 5.5.18?Rik Wasmus7 Jul
        • Re: Subquery taking too much time on 5.5.18?Cabbar Duzayak7 Jul
          • Re: Subquery taking too much time on 5.5.18?Peter Brawley7 Jul
            • Re: Subquery taking too much time on 5.5.18?Cabbar Duzayak8 Jul
  • RE: Subquery taking too much time on 5.5.18?Rick James16 Jul