List:General Discussion« Previous MessageNext Message »
From:John Daisley Date:July 21 2009 6:16pm
Subject:Re: Index selection problem
View as plain text  

On Tue, 2009-07-21 at 19:42 +0200, Morten Primdahl wrote:

> On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote:
> > MySQL is unable to use your index when you use IN and/or OR on yoru  
> > column.
> Is this really true?

No its not true! Try running OPTIMIZE TABLE on the affected table, then
run the query again and see if the other index is used!


> I'm reading "High Performance MySQL 2nd ed." these days and  
> specifically got the impression that using IN will allow usage of the  
> index. The below quote is from the book, and the "multiple equality  
> condition" refers to an IN (...) expression.
> "... we draw a distinction between ranges of values and multiple  
> equality conditions.The second query is a multiple equality condition,  
> in our terminology. We’re not just being picky: these two kinds of  
> index accesses perform differently. The range condition makes MySQL  
> ignore any further columns in the index, but the multiple equality  
> condition doesn’t have that limitation."

John Daisley
Email: john.daisley@stripped
Mobile: +44 (0)7812 451238

MySQL Certified Database Administrator (CMDBA)
MySQL Certified Developer (CMDEV)
MySQL Certified Associate (CMA)
Comptia A+ Certified Professional IT Technician


Life's journey is not to arrive at the grave safely in a well preserved
body, but rather to slide in sideways, thoroughly used up, totally worn
out and screaming "Wow! what a ride!"

Index selection problemMorten21 Jul
  • Re: Index selection problemJohnny Withers21 Jul
    • Re: Index selection problemMorten Primdahl21 Jul
      • Re: Index selection problemJohn Daisley21 Jul
        • Re: Index selection problemJohnny Withers23 Jul
  • Re: Index selection problemBrent Baisley21 Jul
    • Re: Index selection problemMorten Primdahl21 Jul