How to index two date columns for this kind of query

Question

I'm having a MySQL-Table like this:

CREATE TABLE `dates` (
`id`  int UNSIGNED NULL AUTO_INCREMENT ,
`object_id`  int UNSIGNED NOT NULL ,
`date_from`  date NOT NULL ,
`date_to`  date NULL ,
`time_from`  time NULL ,
`time_to`  time NULL ,
PRIMARY KEY (`id`)
);

which is queried mostly this way:

SELECT object_id FROM `dates`
WHERE NOW() BETWEEN date_from AND date_to

How do I index the table best? Should I create two indexes, one for date_from and one for date_to or is a combined index on both columns better?

Answer 1

For the query:

WHERE NOW() >= date_from 
  AND NOW() <= date_to

A compound index (date_from, date_to) is useless.

Create both indices: (date_from) and (date_to) and let the SQL optimizer decide each time which one to use. Depending on the values and the selectivity, the optimizer may choose one or the other index. Or none of them. There is no easy way to create an index that will take both conditions into consideration.

---

(A spatial index could be used to optimize such a condition, if you could translate the dates to latitude and longitude).

Update

My mistake. An index on (date_from, date_to, object_id) can and is indeed used in some situations for this query. If the selectivity of the NOW() <= date_from is high enough, the optimizer chooses to use this index, than doing a full scan on the table or using another index. This is because it's a covering index, meaning no data is needed to be fetched from the table, only reading from the index data is required.

Minor note (not related to performance, only correctness of the query). Your condition is equivalent to:

WHERE CURRENT_DATE() >= date_from 
  AND ( CURRENT_DATE() + INTERVAL 1 DAY <= date_to
       OR  ( CURRENT_DATE() = NOW() 
         AND CURRENT_DATE() = date_to
           )
      )

Are you sure you want that or do you want this:

WHERE CURRENT_DATE() >= date_from 
  AND CURRENT_DATE() <= date_to

The NOW() function returns a DATETIME, while CURRENT_DATE() returns a DATE, without the time part.