I recently posted these questions in order to try and improve the performance for my queries.
One of the suggestions was that I should use a
DATE() column rather than columns such as
day (which was how the data was given to me). Given the built in functions. However when I changed over to this, it slowed down a couple of my queries to the point where they aren't suitable for using on a web page. Following this, I decided to change back to the original way of using a year column, and a yearmonth column (theyre the granularities the page looks at), and my queries run in less than a few seconds.
I often see posts which 'condemn' this method of searching a table, however I can execute queries in a tenth of the time the 'less recommended way'.
If it is bad practice, why is it?
Is it a case of, if it works better that way, then use it? Or should I really not be seeing a 10x difference in query duration between the two?
Edit: My use case is write once, read lots, and the date values in rows will never change.