Hi Michael, Jochem,
----- Original Message -----
From: "Michael Stassen"
Sent: Tuesday, March 16, 2004 10:00 AM
Subject: Re: BETWEEN
>
> Jochem van Dieten wrote:
>
> > However, I expect that would result in doing 2 rangescans and a
> > merge. It might be even faster to use:
> > SELECT * FROM sys
> > WHERE sectorID BETWEEN 1 AND 42
> > AND sectorID NOT BETWEEN 21 AND 29;
> >
> > That would result in 1 rangescan and a filter. But a lot depends
> > on the schema and cardinality.
>
> I'm curious. Could you explain this further? If this works by
grabbing
> 1 to 42, then dropping 21 to 29, wouldn't this increase the likelihood
> (without knowing anything about the data) of crossing the 30%
threshhold
> and doing a tablescan instead of using the index, relative to asking
for
> 1 to 20 plus 30 to 42?
>
> On the other hand, if you already know that 1 to 20 and/or 30 to 42
> exceeds 30%, then one tablescan is better than 2. Is that what you
mean?
>
> Also, if we ignore the UNION version and look at the two BETWEENs, are
> you saying that
>
> WHERE sectorID BETWEEN 1 AND 20
> OR sectorID BETWEEN 30 AND 42;
>
> will be treated differently than
>
> WHERE sectorID BETWEEN 1 AND 42
> AND sectorID NOT BETWEEN 21 AND 29;
>
> by the optimizer?
No, no, no. :-) Well, yes, it will be treated differently, in that the
latter won't be as optimized. You should always only use
WHERE sectorID BETWEEN 1 AND 20
OR sectorID BETWEEN 30 AND 42;
Which, assuming sectorID is an integer (not a float etc.), will be
treated exactly like an IN (...) list with 32 values (or 32 ORs; but
BETWEEN is the simplest and parses fastest). Using the larger BETWEEN
range with NOT BETWEEN will cause who-knows-how-many rows with sectorID
between 21 and 29 to be read, only to be discarded!
Of course, it's possible that MySQL could remove the range values that
won't be found because they conflict, but it only does that type of
thing with constants, not ranges; at least not yet.
Compare the EXPLAINs for these 2 WHEREs:
... WHERE col=123 AND col <> 123;
... WHERE col IN (123) and col NOT IN (123);
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
