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?
Yes. But how relevant is that if the entire table fits on one page and is a tablescan necessarily slower then an indexscan when returning 29% of the table?
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?
That was the scenario I had in mind.
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, I am saying that depending on cardinality and schema it might be treated differently.
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]
