List:General Discussion« Previous MessageNext Message »
From:Eamon Daly Date:December 12 2005 8:48pm
Subject:Re: NULL, OR, and indexes
View as plain text  
Yep, I forgot to mention that I'm using MySQL 4.1, which
does support ref_or_null, but only for the first column.

____________________________________________________________
Eamon Daly



----- Original Message ----- 
From: "Dan Nelson" <dnelson@stripped>
To: "Eamon Daly" <edaly@stripped>
Cc: <mysql@stripped>
Sent: Monday, December 12, 2005 2:45 PM
Subject: Re: NULL, OR, and indexes


> In the last episode (Dec 12), Dan Nelson said:
>> In the last episode (Dec 12), Eamon Daly said:
>> > I'm sure the answer is "You're SOL", but I figured I'd ask anyway. I
>> > have a WHERE condition like:
>> >
>> > SELECT * FROM a JOIN b WHERE
>> > (b1 IS NULL OR b1 = u1) AND
>> > (b2 IS NULL OR b2 = u2) AND
>> > (b3 IS NULL OR b3 = u3)
>> >
>> > where b is a Very Large table. I have an index on b like (b1, b2,
>> > b3), but obviously that gets thrown out because of the OR. Is there a
>> > Better Way to either rewrite the WHERE condition or break out b to
>> > allow indexes to be used?
>>
>> What's the explain plain look like, and how many rows does your query
>> return on average?  The OR shouldn't prevent an index lookup, at least
>> for the simple one-column index case.  It can use a ref_or_null lookup.
>> What happens if you force the use of your composite index with a hint?
>
> And to answer my own question:
> http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html
> explains that ref_is_null only works on the first part of a compound
> index.
>
> -- 
> Dan Nelson
> dnelson@stripped
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
> 

Thread
NULL, OR, and indexesEamon Daly12 Dec
  • Re: NULL, OR, and indexesDan Nelson12 Dec
    • Re: NULL, OR, and indexesDan Nelson12 Dec
  • Re: NULL, OR, and indexesEamon Daly12 Dec