How to make MySQL query performant WHERE one column uses greater than and one column uses less than?
Having two &quot;ranges&quot; is not easy to optimize.  This may help:
INDEX(foo_id, enabled, start_time, end_time)
INDEX(foo_id, enabled, end_time, start_time)

(As you said...)  The optimizer will pick between those two &quot;composite&quot; indexes based on statistics that may tell it which range is shorter.  It will quickly check through the two = tests (foo_id and enabled; btw, they can be in either order).  Then it will scan through the 3rd value.  The final column is there in hopes that &quot;Using index condition&quot; (aka 'ICP') helps.
The reason for having 2 indexes -- the length of one of the scans is likely to be less than the other.
Is that the entire query?  Anything added may throw a monkey wrench into the analysis.
I don't think the time syntax with 'T' and 'Z' will work in MySQL.
A more complex solution...  Can the start_time..end_time ranges overlap?  If not, an optimization can take advantage of that.  http://mysql.rjweb.org/doc.php/ipranges