Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 206
Toby Avatar asked Oct 01 '10 14:10

Toby


People also ask

Can we index date field in MySQL?

This makes your datetime column an excellent candidate for an index if you are going to be using it in conditions frequently in queries. If your only condition is BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY) and you have no other index in the condition, MySQL will have to do a full table scan on every query.

Can we CREATE INDEX on datetime column?

we solved it by creating index on datetime column . yes , cluster index will give better performance over non cluster indexes because ultimately non cluster index use cluster indexes internally .

Can you index a text field MySQL?

MySQL has support for full-text indexing and searching: A full-text index in MySQL is an index of type FULLTEXT . Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR , VARCHAR , or TEXT columns.

How do I create a date row in MySQL?

The default way to store a date in a MySQL database is by using DATE. The proper format of a DATE is: YYYY-MM-DD. If you try to enter a date in a format other than the Year-Month-Day format, it might work but it won't be storing the dates as you expect.


1 Answers

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.

like image 178
Daniel Vassallo Avatar answered Oct 05 '22 07:10

Daniel Vassallo