I've been struggling all day long against this.
We have a very busy database (still on development) and we have records being inserted very frequently. The record logs have a start time and a end time.
If I want to select something between
datetime) MySQL can't use indexes properly because it will search the index for
col1 but will never look into
The database engine is InnoDB. What happens is for example that MySQL will search 80 thousand rows when the interval requested should only return two rows.
My biggest problem is that I'm trying to do some aggregate functions on this time ranges and its taking a very long time when it should be really fast considering how many rows it is actually counting.
Also note that I can't do
dateStart between col1 and col2 nor
dateEnd between col1 and col2 because
dateStart can be lower than
dateEnd can also be lower than
Lets assume this sample data:
col1 | col2 ---------------+--------------- date 10:20:00 |date 10:21:00 date 10:21:00 |date 10:22:00 date 10:22:00 |date 10:23:00 date 10:23:00 |date 10:24:00 date 10:24:00 |date 10:25:00 date 10:25:00 |date 10:26:00 date 10:26:00 |date 10:27:00
If I need the rows that range between 10:21:30 and 10:25:30 I need to do something like this:
'10:25:30' <= col1 AND '10:21:30' >= col2.
So how do I index these columns properly? MySQL only picks up one of the date columns in the index.