> query B can not used any key because 'like' never can use any key Not true. LIKE without a leading wildcard is optimized like a BETWEEN.
> -----Original Message----- > From: Reindl Harald [mailto:h.rei...@thelounge.net] > Sent: Friday, July 06, 2012 8:58 AM > To: mysql@lists.mysql.com > Subject: Re: Subquery taking too much time on 5.5.18? > > > > Am 06.07.2012 17:46, schrieb Cabbar Duzayak: > > 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. > > query B can not used any key because 'like' never can use any key > > i try to avoid subqueries wherever i can becasue the mysql query- > optmizer is really weak in most cases (it appears > 5.6 will be much better in many of them) > > such things i would always do with two queries in the application > > * first the sub-query > * genearte the query above with the results in the app > * fire up the final query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
