MySQL Forums
Forum List  »  Performance

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.

INDEX(type)
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.

Any of
INDEX(start)
INDEX(end)
INDEX(start,end)
INDEX(end,start)
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:
INDEX(type, start)
INDEX(type, end)
One of those will have a fighting chance to optimize the SELECT. And, by having both, you give the optimizer the choice.

Options: ReplyQuote


Subject
Views
Written By
Posted
3794
September 11, 2009 03:48AM
Re: field equal to OR between 2 fields?
4741
September 12, 2009 09:03AM
2083
September 12, 2009 11:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.