List:General Discussion« Previous MessageNext Message »
From:Rick James Date:July 16 2012 9:57pm
Subject:RE: Subquery taking too much time on 5.5.18?
View as plain text  
> 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.reindl@stripped]
> Sent: Friday, July 06, 2012 8:58 AM
> To: mysql@stripped
> 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

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