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

If you are using MySQL 5.0, try creating three separate indexes, one
for each column, and see if index merge optimization does any good. 
Yyou may have to force it to use those three indexes using a hint; the
optimizer isn't very smart about merges yet.

-- 
	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