I have a table with a field
eventtime of type datetime and a different table with a datetime field.
I need the minimum
eventtime that is greater than the greatest datetime field from the other table which may not have any rows. If there are no rows in the other table, I just need the minimum eventtime.
My query is
SELECT min(eventtime) time_min FROM `cel` WHERE eventtime > (SELECT COALESCE(MAX(other_datetime), 0) FROM `call`)
This gives me exactly what I want. The problem is there are about 6.5 million records in the table and this query takes about 10 or 11 seconds to run.
I tried using other values instead of
0 in the coalesce such as an empty string, and
'1000-01-01 00:00:00' thinking that a type conversion was causing a performance hit, but those did not help at all.
- Should I add an index on the eventtime field?
0an appropriate value to compare against a datetime field?
- If not, what is the right way to represent the minimum datetime value for comparison?
- Is there a better way to achieve this to optimize for execution time?