Re: Query execution for intervals

From: <>
Date: 5 Feb 2006 08:27:21 -0800
Message-ID: <> wrote:
> Hi,
> 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

Not sure what you mean by linear time, but for this query (IMHO) it is probably better with an index like:

create index x1 on calendar_event (event_start, event_end)

than to have two separate indexes. Also, for this query it probably wont help, but it migth be a good idea to inform the optimizer that event_start <= event_end

alter table calendar_event add constraint whatever check (event_start, event_end)

/Lennart Received on Sun Feb 05 2006 - 17:27:21 CET

Original text of this message