CODEWITHSUNDEEP
mysqlindexingdatabase-indexes
Toby
Toby

Reputation: 8792

Putting an index on a date field in MySQL

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.

Upvotes: 5

Views: 5468

Answers (1)

Daniel Vassallo
Daniel Vassallo

Reputation: 344561

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.

Upvotes: 16

Related Questions