After reading the Questions and Answers from this website about indexes, a question came to my mind.
What if, one is using a time dimension table with the lower level of granularity being the day. Where should one put the indexes ?
Randy Melder in the question : What does “index” means on RDBMS ? said :
Think of an index as "table of contents"... that is an ordered list of pointers to positions in a file, aka offsets
In the case of the time dimension, most data research might be done either for a specific day, a specific week, a specific month or a specific quarter if the time table stores all the day for a unique year.
My question is : Should one put indexes for all those fields ?
Day is suppose to be unique so for this one I understand perfectly the use of indexes. But a week id will have 7 occurences, a month id will have 30/31 occurences, a quarter id will have more or less 120 occurences.
- Should one still put indexes for those fields ?
- Will it still be useful?
I am asking you that because in the same question, David Spillett said :
Adding too many indexes can be a bad optimisation of course, as the extra space used to store the indexes (and the IO-load to maintain them if your DB sees many write operations) may be a worse problem than the slightly less optimal read queries, so don't over-do it.
So what would be the best considerations for the time dimension case ?