Select where datetime is greater than other datetime which may not exist 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 &gt; (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.

mysql explain for the query



Should I add an index on the eventtime field?
Is 0 an 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?
Pull the COALESCE out of the subquery, to make it sargable:

        SELECT  min(eventtime) time_min
            FROM  `cel`
            WHERE  eventtime &gt; 
                COALESCE( ( SELECT  MAX(other_datetime)
                                   FROM  `call` ), 0)


And indexes:

call:  INDEX(other_datetime)
cel:   INDEX(eventtime)


That should be able to use both indexes to good advantage and take essentially zero time.

If there is an issue with 0 not being a date, try "0000-00-00"