List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:March 18 2004 6:36am
Subject:Re: BETWEEN
View as plain text  
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

Thread
BETWEENKeith13 Mar
  • Re: BETWEENMichael Stassen13 Mar
    • Re: BETWEENJochem van Dieten13 Mar
      • Re: BETWEENMichael Stassen16 Mar
        • Re: BETWEENJochem van Dieten16 Mar
  • Re: BETWEENRhino13 Mar
  • Re: BETWEENMatt W13 Mar
    • Re: BETWEENMichael Stassen16 Mar
      • RE: BETWEENBoyd E. Hemphill20 Apr
        • RE: BETWEENMax Michaels20 Apr
  • Re: BETWEENMatt W18 Mar
    • Re: BETWEENMichael Stassen18 Mar
  • Re: BETWEENMatt W18 Mar
Re: BETWEENbeacker13 Mar
RE: BETWEENBoyd E. Hemphill20 Apr
  • RE: BETWEENAndy Eastham20 Apr
    • RE: BETWEENBoyd E. Hemphill21 Apr
  • Re: BETWEENBrian Reichert20 Apr