Date-storing tests in MySQL; Part 2

The results are in. First, there was a little thing I forgot about when describing the test to you earlier. Namely, storage size. The amount of storage required is also another thing to think about.

The relevant sizes of MySQL data types:

• DATE: 3 bytes
• INT: 4 bytes

Looking at my 3 ideas, one should be thrown out right away. Having three integers, even reducing them to 2 TINYINT's (1 byte) and 1 SMALLINT (2 bytes), we are still talking 4 bytes, and thats with a lot of extra complexity. So that was out.

I generated a dataset with a little over 7 million rows, with data looking like:

[sourcecode language="sql"] mysql> SELECT * FROM crunchme LIMIT 10; +------------+------------+----------+--------+---------+-------+ | idcrunchme | adate | timdate | ayear | amonth | a_day | +------------+------------+----------+--------+---------+-------+ | 12111 | 2009-01-01 | 20090101 | 2009 | 1 | 1 | | 12112 | 2011-03-03 | 20110303 | 2011 | 3 | 3 | | 12113 | 2008-11-19 | 20081119 | 2008 | 11 | 19 | | 12114 | 2009-07-16 | 20090716 | 2009 | 7 | 16 | | 12115 | 2008-10-19 | 20081019 | 2008 | 10 | 19 | | 12116 | 2009-04-22 | 20090422 | 2009 | 4 | 22 | | 12117 | 2009-04-03 | 20090403 | 2009 | 4 | 3 | | 12118 | 2011-06-10 | 20110610 | 2011 | 6 | 10 | | 12119 | 2009-06-01 | 20090601 | 2009 | 6 | 1 | | 12120 | 2008-08-24 | 20080824 | 2008 | 8 | 24 | +------------+------------+----------+--------+---------+-------+ [/sourcecode]

Let's see what happens when we run some queries!

[sourcecode language="sql"] mysql>SELECT SQLNOCACHE count(idcrunchme) FROM crunchme WHERE timdate >= 20100101 AND timdate <= 20110514;

+-------------------+ | count(idcrunchme) | +-------------------+ | 2388242 | +-------------------+ 1 row in set (2.96 sec)

mysql>SELECT SQLNOCACHE count(idcrunchme) FROM crunchme WHERE adate >= '2010-01-01' AND adate<='2011-05-14'; +-------------------+ | count(idcrunchme) | +-------------------+ | 2388242 | +-------------------+ 1 row in set (3.61 sec) [/sourcecode]

So, the integer solution is faster, by roughly 22% at this level. However, here is where it gets fun. It is larger, by one byte. Also, it will mean having to add extra code when I'm working with dates in my CodeIgniter app.

After all this, I am going to go with the MySQL DATE type. I was surprised to see the time difference being so low.

I hope this helps someone else out!