3

I have such a table:

id | content | date
45 | "Lorem" | "2014-09-06"
56 | "Ipsum" | "2013-05-01"

The table has a lot of rows. Now I need to get different year values.

Statement:

SELECT YEAR(`date`) AS `year` FROM `news` GROUP BY `year` ORDER BY `date`

Unfortunately, this solution doesn't use date index.

My question is if it's a good practice to have a separate year column and set it before every insert/update and have an index on it?

Or is there a better solution?

ekad
  • 14,436
  • 26
  • 44
  • 46
Craft
  • 123
  • 11

1 Answers1

0

A time-space tradeoff if you ask me. In any case, to maintain normalization, you need to exclude the year from the date or never use it for the purposes the year column exists for.

A possible implementation is to use insert/update triggers as per Is it possible to have function-based index in MySQL? . This shouldn't hurt performance much since news are likely to be added/updated much less frequently than read.

Community
  • 1
  • 1
ivan_pozdeev
  • 33,874
  • 19
  • 107
  • 152
  • 2
    I agree. MySQL does neither support computed columns nor function-based indexes, so simply add a year column and have insert and update triggers care about it. – Thorsten Kettner Sep 09 '14 at 12:00