Matt W wrote:
Hi Michael,
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;
The query using 2 BETWEENs with OR is exactly how it should be. It will be fast even in MySQL 3.23. OR is not a problem when the OR parts involve the same index. :-)
Well, that makes sense, and it fits my own experience, but is it documented anywhere? Or is that just supposed to be common sense?
So far as I can see, the manual does not mention OR or BETWEEN in the context of using an index, except for the mention of a new feature in 5.0 <http://www.mysql.com/doc/en/OR_optimizations.html>. There have been so many threads about slowness of OR or BETWEEN, often with UNION as a suggested work-around, that in the absence of a clear description of how this works and when UNION helps, it is easy to get confused.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
