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

    min(eventtime) time_min
    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.

mysql explain for the query 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?
  • Does the indexes call (other_datetime) and cel (eventtime) exists? Or at least the last one... – Akina Dec 24 '19 at 4:50
  • 1
    Is 0 an appropriate value to compare against a datetime field? It may be incompatible to current SQL_Mode. Some constant literal value less than MIN(cel.eventtime) is more safe (for example, '1970-01-01 00:00:01'). – Akina Dec 24 '19 at 4:53
  • There are currently no indexes for the datetime fields on either table. – Stack Underflow Dec 24 '19 at 4:56
  • Create index by cel (eventtime). Call table is compact enough, and selecting maximal field value using fullscan should not cause to visible performance degrade. – Akina Dec 24 '19 at 4:59

Pull the COALESCE out of the subquery, to make it sargable:

        SELECT  min(eventtime) time_min
            FROM  `cel`
            WHERE  eventtime > 
                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"

  • Performance aside, moving COALESCE outside the sub-select will work for an empty call table. In the OP:s query the result of the sub-query will be null if there are no rows in call. I.e your answer is an improvement for the logic as well – Lennart Jun 27 '20 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.