MySQL Forums
Forum List  »  Performance

Date range
Posted by: Martin Gunnarsson
Date: February 23, 2005 09:57AM

I have quite a tricky problem with a booking system that I'm working on. In my Booking table I have two fields, starttime and endtime, both being DateTime. When I want to get the bookings for a certain period, I have to run a query like this, where [start] and [end] are the start and end times of the period:

SELECT * FROM Booking WHERE
(startTime >= [start] AND startTime < [end]) OR
(endTime > [start] AND endTime <= [end]) OR
(startTime < [start] AND endTime > [end])

It feels like this query could be made in a much smarter way, but let me explain what the problem is. If I run the three conditions separately the first two are fairly quick, 20 ms including network delay with close to 60000 bookings. The last one though is a lot slower, and the whole query takes around 200 ms to execute. I have indexed the startTime and endTime fields, but it seems like MySQL can only use one of these indexes to speed up the query. Questions with only one indexed field are quick, but with two it gets really slow. Adding both fields to the same index doesn't help.

Any suggestions both how to write this query in a better way, and how to speed it up using some idex wizardry are greatly appreciated.

Thanks in advance!

--
Martin Gunnarsson
Sweden

Options: ReplyQuote


Subject
Views
Written By
Posted
Date range
2771
February 23, 2005 09:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.