0

I'd like to make this query perfomant--

SELECT * 
FROM my_table
WHERE foo_id = 8
AND enabled = true
AND start_time < '2020-09-12T06:59:59.999Z'
AND end_time > '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 < '2020-09-12T06:59:59.999Z'
AND id IN (
  SELECT id
  FROM my_table
  WHERE end_time > '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 > '2020-09-07T07:00:00.000Z'
AND enabled = true
AND start_time < '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.

  • 1
    Have you tried creating an index on (end_time ASC, start_time DESC)? – Gordan Bobic Aug 30 at 6:13
  • Please add a descritption of my_table and its indexes. Is "enabled" selective (how many true, how many false or null) ? – Gerard H. Pille Aug 30 at 8:44
  • Please consider reading about Asking query performance questions – mustaccio Aug 30 at 14:21
  • What do start_time and end_time represent here? my_table doesn't give a lot of context and the best solution will require understanding if end_time is truly required or just an artifact of a sub-optimal data model. – bbaird Aug 30 at 19:10
0

Having two "ranges" 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 "composite" 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 "Using index condition" (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

| improve this answer | |

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.