How to make MySQL query performant WHERE one column uses greater than and one column uses less than? I'd like to make this query perfomant--
SELECT * 
FROM my_table
WHERE foo_id = 8
AND enabled = true
AND start_time &lt; '2020-09-12T06:59:59.999Z'
AND end_time &gt; '2020-09-07T07:00:00.000Z'

This is tricky because I need less than on start_time and greater than on end_time.  If there is a composite index on foo_id, enabled, start_time, end_time the index is good at filtering down all the rows with foo_id, enabled, and start_time matching the criteria--but then does a full scan to find the rows that match end_time.
My best efforts at optimizing so far are--
SELECT * 
FROM my_table
WHERE foo_id = 8
AND enabled = true
AND start_time &lt; '2020-09-12T06:59:59.999Z'
AND id IN (
  SELECT id
  FROM my_table
  WHERE end_time &gt; '2020-09-07T07:00:00.000Z'
)

and
SELECT * 
FROM my_table
WHERE foo_id = 8
JOIN my_table AS t ON t.id = my_table.id
  WHERE t.end_time &gt; '2020-09-07T07:00:00.000Z'
AND enabled = true
AND start_time &lt; '2020-09-12T06:59:59.999Z'

Both of these efforts try to use two indexes separately: the one on foo_id, enabled, start_time and the other on end_time.  They are both faster.  But I'd like to avoid a query with a large IN list.  And doing the JOIN just to sneak in a WHERE doesn't feel right.
Maybe this is good enough?  INTERSECTION would serve well here.  This seems like it would be common problem, so curious if I'm missing an obvious/better solution.
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