Re: field equal to OR between 2 fields?
Posted by: Rick James
Date: September 12, 2009 09:03AM
Select performance depends on, in order,
1. The number of disk hits
2. The number of rows "examined"
Without indexes, they are the same -- namely a full table scan. A "table scan" "examines" all the rows in the table. And if the table is bigger than can be cached in ram, that also means a lot of disk hits.
With indexes, it depends.
Situation 1: Each row has a different "type": The index would make that SELECT very fast. 1 row examined (maybe 2, but that's another details); maybe one disk hit. Very fast, even for a huge table.
Situation 2: 90% of the rows have type=1. Well, it's back to a table scan.
Situation 3: `start` <= '2010-06-01 14:50:20' and less than 30% of the rows satisfy that inequality. Then INDEX(start) or (start,end) would be used.
Situation 4: etc, etc.
Probably you will get mixed results on anything you try.
Or is this your case?
SELECT ... WHERE type = 1 AND start <= ... AND end >= ...
In this case, I recommend having two indexes:
One of those will have a fighting chance to optimize the SELECT. And, by having both, you give the optimizer the choice.