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