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:cab...@gmail.com] Sent: Friday, July 06, 2012 11:46 AM To: mysql@lists.mysql.com 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
