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 yr, yrmth(e.g. 201502), mth, 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.

  • 1
    It's bad practice because it relies on having two or more columns being in sync for a single value. If the date needs to be changed, then both the date, the yr and yrmth (and any others) need to be changed as well. Did you apply indexes to the "Date" column after creating it? Have you considered the use of a calendar table and joining on that to get various periods? – Jonathan Fite Mar 28 '16 at 16:13
  • 1
    Are the queries converting the values using the DATE() function or are you converting the values and storing them in a date data type column? – Antoine Hernandez Mar 28 '16 at 16:13
  • For my use case, the tables are write once and the dates will never be updated - there are also several million rows. I did index the date() column when I used it. When I did use the DATE() column, I used actual DATE() functions in the queries, for example WHERE tdate >= '2014-01-01' AND tdate < '2014-01-01' + INTERVAL 1 YEAR. This was significantly slower than WHERE yr = '2014' or WHERE yrmth = '201502'. – Adam Copley Mar 28 '16 at 16:18
  • If I understand correctly, your queries were probably slower due to using the function since the table values never changed whereas the "bad way" uses the values as they exist in the table. Have you tried copying the table data to a test table, creating a new column with the date data type, storing the date in that column, and checking query result times? – Antoine Hernandez Mar 28 '16 at 17:06
  • 1
    And have INDEX(tdate). – Rick James Mar 28 '16 at 23:17

It is a bad practice in a transactional/operational database and a good practice in an analytic/data warehouse database.

In a data warehouse I recommend using an integer "smart key"† for your date dimension (like 20160328), but also including an actual date-type column, as well as the broken-down date parts.

The broken down date parts are more for ease of analysis, than speed of querying.

† This makes it easy to partition your fact tables.

  • I disagree. Having a dimension table for any "continuous" value (date, datetime, float, etc) becomes inconvenient and inefficient for doing range queries. I would argue that most date tests are range. – Rick James Mar 28 '16 at 23:19
  • @RickJames who said anything about times? Not having a date dimension in a data warehouse would be the oddest thing ever – Neil McGuigan Mar 28 '16 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.