Query execution for intervals

From: <frebe73_at_gmail.com>
Date: 4 Feb 2006 00:15:02 -0800
Message-ID: <1139040902.571976.56310_at_o13g2000cwo.googlegroups.com>

I have a table representing events in a calendar event(id, start, end, ....)

I have two indexes, one on the start column and one on the end column.

I have a select query for finding all events containing a given time select *
from event
where start <= :time and end >= :time

Is it possible for the DBMS to execute this query faster than linear time? From my understanding the DBMS can use one of the indexes, lets say the start index, to find every event starting before the given time. Then all these events has to be traversed to check if the end time is after the given time. The only thing the query optimizer migth do is to choose to use the the start index or the end index depending on the time value. But it will still be a linear search.

Are my assumptions correct? Are any way to change the database structure to enable a search faster than linear time.

Best regards,
frebe Received on Sat Feb 04 2006 - 09:15:02 CET

Original text of this message