From: Peter Brawley
Date: July 7 2012 2:12am
Subject: Re: Subquery taking too much time on 5.5.18?
List-Archive: http://lists.mysql.com/mysql/227783
Message-Id: <4FF79AFE.3030601@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


On 2012-07-06 5:07 PM, David Lerer wrote:
> 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%');

See "The unbearable slowness of IN()" at 
http://www.artfulsoftware.com/infotree/queries.php

SELECT a.*
FROM a
JOIN b USING(a_id)
WHERE B.name LIKE 'X%';

PB

-----


>
> 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.
>