Is it good practice to have a separate column with the date of the year to improve performance?
I have a table like this:
id | content | date
45 | "Lorem" | "2014-09-06"
56 | "Ipsum" | "2013-05-01"
There are many rows in the table. 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 an index date
.
My question is, is it good practice to have a separate column year
and set it before every insert / update and have an index on it?
Or is there a better solution?
source to share
A time-to-time communique , if you ask me. In any case, to maintain normalization, you need to exclude the year from the date or never use it for purposes where the year column exists.
A possible implementation is to use insert / update triggers according to Is it possible to have a functional index in MySQL? ... This shouldn't degrade performance much as news may be added / updated much less frequently than read.
source to share