Jochem van Dieten wrote:
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.
Right, because UNION DISTINCT is the default. Good point.
Matt W has already pointed out, however, that UNION is unnecessary, as the optimizer has no problems with OR when the same index is used.
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?
Jochem
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
