List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:July 6 2012 3:58pm
Subject:Re: Subquery taking too much time on 5.5.18?
View as plain text  

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


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Thread
Subquery taking too much time on 5.5.18?Cabbar Duzayak6 Jul
  • Re: Subquery taking too much time on 5.5.18?Reindl Harald6 Jul
    • RE: Subquery taking too much time on 5.5.18?Benjamin Stillman6 Jul
      • Re: Subquery taking too much time on 5.5.18?Cabbar Duzayak6 Jul
    • RE: Subquery taking too much time on 5.5.18?Rick James16 Jul
  • RE: Subquery taking too much time on 5.5.18?David Lerer6 Jul
    • Re: Subquery taking too much time on 5.5.18?Peter Brawley7 Jul
      • Re: Subquery taking too much time on 5.5.18?Rik Wasmus7 Jul
        • Re: Subquery taking too much time on 5.5.18?Cabbar Duzayak7 Jul
          • Re: Subquery taking too much time on 5.5.18?Peter Brawley7 Jul
            • Re: Subquery taking too much time on 5.5.18?Cabbar Duzayak8 Jul
  • RE: Subquery taking too much time on 5.5.18?Rick James16 Jul