List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:December 12 2005 8:45pm
Subject:Re: NULL, OR, and indexes
View as plain text  
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
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