Does it improve performance to index a date column?

Question

I have a table with millions of rows where one of the columns is a TIMESTAMP and against which I frequently select for date ranges. Would it improve performance any to index that column, or would that not furnish any notable improvement?

EDIT:

So, I've indexed the TIMESTAMP column. The following query select count(*) from interactions where date(interaction_time) between date('2013-10-10') and date(now())

Takes 3.1 seconds.

There are just over 3 million records in the interactions table.

The above query produces a result of ~976k

Does this seem like a reasonable amount of time to perform this task?

Answer 1

If you want improvement on the efficiency of queries, you need 2 things:

• First, index the column.

• Second, and this is more important, make sure the conditions on your queries are sargable, i.e. that indexes can be used. In particular, functions should not be used on the columns. In your example, one way to write the condition would be:

  WHERE interaction_time >= '2013-10-10' 
    AND interaction_time < (CURRENT_DATE + INTERVAL 1 DAY)