views:

17

answers:

1

Is there going to be any real benefit to me putting indexes onto date fields that are going to be mainly used in queries using stuff like.

dateField < 'var'

And

'var' BETWEEN dateField1 AND dateField2

The searches get done a lot but I am never doing a direct comparison "=" on them.

+5  A: 

Yes of course. Range searches will benefit from an index, just as much as equality searches.

Quoting from the MySQL Reference Manual :: How MySQL Uses Indexes:

B-Tree Index Characteristics

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

In some situations, the optimiser may decide not to use the index if the range will end up to be too big, as a table scan might actually be faster. Use EXPLAIN to see which (if any) indexes will be used on your query.

Daniel Vassallo