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]
