Matt W wrote:
Hi Michael,<snip>
http://www.mysql.com/doc/en/MySQL_indexes.html - Under the example "WHERE clauses that use indexes":
/* index = 1 OR index = 2 */ ... WHERE index=1 OR A=10 AND index=2
And for clarity, that should really have parentheses: index=1 OR (A=10 AND index=2)
Thanks for pointing that out. I have to admit that, without the parentheses, I believe I misread this. Really, if we care about clarity, we should just drop the A=10. That is, change this to
/* index = 1 OR index = 2 */ WHERE index=1 OR index=2
or at least add that as an example before the version with A=10 (which should definitely have the parentheses, as you suggest).
It's not a lot, but I don't think there ever was much about it in the docs -- except maybe pointing out that indexes wouldn't be used if the [top-level] OR branches don't reference the same indexed column (before 5.0). I don't see that mentioned anymore however... except indirectly in that "How MySQL Optimizes OR Clauses" section you mentioned.
Note that the "How MySQL Uses Indexes" page explicitly states "An index is used for columns that you compare with the =, >, >=, <, <=, or BETWEEN operators." No mention of IN, which at least implies not for that case. Surely this should read, "An index is used for columns that you compare with the =, >, >=, <, <=, IN, or BETWEEN operators."
And there's no mention of combining with OR, other than that one obscure example. An explicit sentence or two somewhere would be very helpful, I think. Something like
Prior to 5.0, an index is used when you combine comparisons with OR, only so long as each part uses the same index. Hence "WHERE index=1 OR index=2" will use the index, but "WHERE index1=1 OR index2=1" will not. See "How MySQL Optimizes OR Clauses" <http://www.mysql.com/doc/en/OR_optimizations.html>.
Matt
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
