CODEWITHSUNDEEP
mysqlsql
user9362338
user9362338

Reputation:

Creating an index for YEAR() function

I'm trying to accelerate a query. This query looks if the year is '2017'. I'm comparing costing between using: LIKE = '2017%', YEAR (date) = '2017' and date BETWEEN '2017-1-1' AND '2017-12-31'.

I would like to make an index for the colum date but using the function year, something similar to:

CREATE INDEX indexDATE ON
table (YEAR(date));

Is this possible?

Upvotes: 2

Views: 847

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The right way to express the logic is:

date BETWEEN '2017-01-01' AND '2017-12-31'

or as I prefer:

date >= '2017-01-01' AND date < '2018-01-01'

This can use an index on (date).

The expression LIKE = '2017%' is simply bad coding. You are using string functions on a date/time column. That is a really bad idea and it precludes the use of indexes.

The expression YEAR (date) = 2017 is logically ok -- once you remove the single quotes around the number. However, the use of the function on the column precludes the use of an index.

Finally, in most data sets, years are not very selective. That is, you are still going to be selecting a significant portion of the rows. Indexes are less useful for such queries.

Upvotes: 3

Related Questions