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
