Suppose I have a table Event for a calendar app:
event_id, start_time, end_time
I want to find all the events that overlap with a given range [s0, e0]. In a calendar App this can be used to retrieve meetings in a week, for example.
How to build indexes to help with such queries? At first I thought we just needed to have two indexes, one on start_time and one on end_time, and db can use the two indexes to get two lists
- events with end_time >= s0
- events with start_time <= e0 and calculate the intersection.
Then I realized that both lists could be very large, so the indexing is not very useful. I was also thinking about composite index (start_time, end_time), but I haven't figured out how composite index can help with range queries. Can anyone give some hints or keywords that I can search for? I feel this should be a common use case but haven't found related questions. Thanks!
