Re: Query execution for intervals

frebe73_at_gmail.com 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)

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