| Mailing List | | Home | | MySQL General - General MySQL discussion | | MaxDB - Everything about MaxDB, formerly known as SAP DB | | MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP | | Java Help - Mostly related to the MySQL Connector/J driver | | ODBC - ODBC with the MySQL Connector/ODBC driver | | Perl - Perl support for MySQL with DBI and DBD::mysql | | MySQL++ - Programming with the C++ API to MySQL |
|
|
 | | | BETWEEN | BETWEEN 2004-03-13 - By Jochem van Dieten
Back
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/mysql?unsub=mysql
@(protected)
|
|
|