asked on

Date Comparison not using Index

Hi All:

I am trying to do the following SQL statement in MySQL:

SELECT DATE_FORMAT(dates.`datum`, '%Y-%m-%d 12:00:00') AS date2,
	(SELECT COUNT(*) FROM bookings 
			WHERE bookings.to >= date2
			AND bookings.from <= date2) AS number_of_bookings


FROM dates

 WHERE dates.`datum` >= '2012-01-01' 
  AND dates.`datum` <= '2012-01-31' 
AND DATE_FORMAT(dates.`datum`, '%H:%i:%s') = '12:00:00'

Open in new window

I have a table "dates" which has all dates from now until 2030 and each day segmented in 15 minute slots, hence the 12:00:00 filter....

The table bookings has (who would have known) bookings which can go over multiple days.

In essence, I want to know who many units are rented out each day.

The above SQL does work, but (using EXPLAIN) I found it will look through the whole bookings table instead of using the indexes which I have on bookings.to and bookings.from thus limiting it to the daterange.

How can I get MySQL to limit where its looking to the index?

Thanks,

T :-)

johanntagle

Can you post the EXPLAIN output? Also try to compare bookings.to and bookings.from to the "native" dates.datum. It might be comparing it to the output of date_format that's killing it. It should at least use one of the indexes (I don't expect it to use both because of the range comparison).

kracklt

ASKER

Hi Johann:

Find the Explain Output enclosed as GIF.

I tried your suggestion to compare with the dates.datum but the explain brought the same solution back.

Any other ideas?

Thomas :-)

johanntagle

Googling about it tells me MySQL is really still deficient when it comes to optimizing subqueries. I currently cannot think of a way to rewrite the query, so my only suggestion right now is for you to save the results of the query to a table so that you can query that table for future reference vs re-running the full query. You can have a script that inserts to the table regulary, maybe daily:

insert into report_table (report_date, total_bookings)
SELECT '2012-03-27 12:00:00', COUNT(*) FROM bookings
WHERE bookings.to >= '2012-03-27 12:00:00'
AND bookings.from <= '2012-03-27 12:00:00';

kracklt

ASKER

Hi johann:

Not sure if that will work since the number of units rented out can change any moment. But you gave me an idea. I wonder if I could make a view and use that as a basis. I will try in the morning and report back.

Thanks for now,

Thomas :-)

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kracklt

ASKER

Hi John:

Sorry for the late reply. I was out of the office unexpectedly yesterday.

Trigger may be a good idea....as you said, I would have to prefill the table (there are 39.000 Entries so far...).....

Too bad. I was hoping I could do this with a simple query :-(

Thank you so mcuh for your help! I will try to go down the trigger way :-)

Thomas :-)

kracklt

ASKER

Unfortunately its not possible to force MySQL to use the index keys, but the provided alternative solution is a great idea :-)

johanntagle

Glad to help =)