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