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
signature.asc
Description: OpenPGP digital signature