I have a table with a date column (NO datetime, date only), which I always use to retrieve information by YEAR using BETWEEN. I was wondering if it's more efficient (and faster) to add a column to store the year, it will be the same year as the date column. Then add an index in the year column. So when I have to retrieve information by year, the where in the select statement will look like this:
WHERE col_year BETWEEN par_year_a AND par_year_b
AND col_date BETWEEN par_date_a AND par_date_b
Where par_year_a is the year from par_date_a, and par_year_b is the year from par_date_b
I believe the other option I have is to add an index to the original date column, without adding a year column. But I don't know if it's better or faster. Seems cleaner, because adding a year column for each date column, in the tables I need to search data in this way, feels like repeating too much data.
I use MySQL 5.6
Thanks for the time and help.
date >= '2022-01-01' and date < '2024-01-01'there are many advantages such as not needing to deal with month ending dates (28,29,30,31) and also not having to worry about time unit precision of the column. But nonetheless do use an index on the date column.