Re: Inner Join using datetime columns, Performance Issues

[Rick James]

Instead of
start.datetime between "2013-12-01 00:00:00" and "2013-12-31 23:59:59"
this may be easier to write:

    start.datetime >= "2013-12-01"
AND start.datetime <  "2013-12-01" + INTERVAL 1 MONTH

state_history would possibly benefit from a compound index:
INDEX(status, status_type, datetime)

I don't see a PRIMARY KEY on state_history; is some combination of fields unique? It almost feels like servicecheck is unique. If it is, then the SELECT probably does not need to JOIN to state_history twice. This would lead to a big improvement.

Do you really need "LEFT" JOIN? It may be impeding the optimizer's ability find a good query plan.