CODEWITHSUNDEEP
mysqldatabase-designdateindexing
acme
acme

Reputation: 14866

How to index two date columns for this kind of query

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?

Upvotes: 10

Views: 4096

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

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.

Upvotes: 7

user1191247
user1191247

Reputation: 12973

You should create an index covering date_from, date_to and object_id as explained by ypercube. The order of the fields in the index is dependant on whether you will have more data for the past or the future. As pointed out by Erwin in response to Sanjay's comment, the date_to field will be more selective if you have more dates in the past and vice versa.

CREATE INDEX ON (date_to, date_from, object_id);

Upvotes: 3

Mithrandir
Mithrandir

Reputation: 25397

How many rows in relation to your table size does your query return? If it's more than 10 percent i would not bother to create an index, in such a case your quite close to a table scan anyway. If it's well below 10 percent, then in this case, would use an index containg (date_from, date_to, object_id) so, that the query result can be constructed entirely from the information in the index, without the database havind to track back to the table data to get the value for object_id.

Depending on the size of your table this can use up alot of space. If you can spare that, give it a try.

Upvotes: 1

Simon at The Access Group
Simon at The Access Group

Reputation: 7025

Create an index with (date_from,date_to) as that single index would be usable for the WHERE criteria

If you create separate indexes then MySQL will have to use one or the other instead of both

Upvotes: 0

Related Questions