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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
