List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:July 22 2009 12:57pm
Subject:Re: Index selection problem
View as plain text  
Maybe I'm wrong :)


On Tuesday, July 21, 2009, John Daisley <john.daisley@stripped> wrote:
>
>
> 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!"
>

-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped
Thread
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