Re: BETWEEN

[Jochem van Dieten]

Michael Stassen wrote:
SELECT * FROM sys
WHERE sectorID BETWEEN 1 AND 20
   OR sectorID BETWEEN 30 AND 42;
If that's slow (the optimizer doesn't like ORs) and you are using at 
least mysql 4.0.0, you can change this to

SELECT * FROM sys WHERE sectorID BETWEEN 1 AND 20
UNION
SELECT * FROM sys WHERE sectorID BETWEEN 30 AND 42;
At the very least use UNION ALL.

However, I xpect 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.
Jochem

--
I don't get it
immigrants don't work
and steal our jobs
    - Loesje


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]