List:General Discussion« Previous MessageNext Message »
From:Rick James Date:July 16 2012 10:02pm
Subject:RE: Subquery taking too much time on 5.5.18?
View as plain text  
How fast does this run?

SELECT  A.*
    FROM  A
    JOIN  B ON B.A_ID = A.id
    WHERE  B.name LIKE 'X%';

Turning a subquery into a JOIN usually improves performance.  (The main exception is when
the subquery consolidates data via GROUP BY, DISTINCT, LIMIT, etc.)


> -----Original Message-----
> From: Cabbar Duzayak [mailto:cabbar@stripped]
> Sent: Friday, July 06, 2012 8: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

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