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