Timestamp vs Datetime in MySQL

A Performance Comparison

This article compares the differences between querying a table containing a timstamp column versus an identical query on a table containing a  datetime column, the findings are quite surprising. This article does not make any conclusions on when to use which datatype, or even suggest any reasons for the differences in performance, it is just meant as a experiment. It does of course show why you should performance test everything within your application, and shows the importance of using good tools to gain visibility into performance testing.

Experiment Test Table:

mysql> desc test;
+-----------+---------------+------+-----+---------------------+-------+
| Field     | Type          | Null | Key | Default             | Extra |
+-----------+---------------+------+-----+---------------------+-------+
| timeslice | timestamp     | YES  |     | 0000-00-00 00:00:00 |       |
| col1      | varchar(20)   | YES  |     | NULL                |       |
| col2      | int(11)       | YES  |     | NULL                |       |
| col3      | varchar(50)   | YES  |     | NULL                |       |
| col4      | varchar(50)   | YES  |     | NULL                |       |
| col5      | int(11)       | YES  |     | NULL                |       |
| col6      | decimal(12,2) | YES  |     | NULL                |       |
| col7      | varchar(50)   | YES  |     | NULL                |       |
| col8      | varchar(100)  | YES  |     | NULL                |       |
| col9      | varchar(50)   | YES  |     | NULL                |       |
+-----------+---------------+------+-----+---------------------+-------+
10 rows in set (0.04 sec)

mysql> show indexes from test;
Empty set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|   241853 |
+----------+
1 row in set (0.00 sec)

mysql> optimize table test;
+-------------+----------+----------+----------+
| Table       | Op       | Msg_type | Msg_text |
+-------------+----------+----------+----------+
| dbtuna.test | optimize | status   | OK       |
+-------------+----------+----------+----------+
1 row in set (0.00 sec)

Size on Disk
test.MYD 12,986 KB

N.B. The MySQL Query Cache is turned off so that caching does not factor in the results. i.e set global query_cache_size=0

Experiment Test query

This query does a full table scan to return a single row with the sum of col2 based on a 10 hour date range.

SELECT sum(col2)
FROM test
WHERE timeslice > date_add( current_timestamp(), interval -600 minute )
AND timeslice < date_add( current_timestamp(), interval 0 minute )

This statement was then executed 100 times against the database in order to get a nice rounded average response time of 0.31 seconds.

The timeslice column was then converted to a datetime column, and the test was repeated.

alter table test modify column timeslice datetime;

mysql> alter table test modify column timeslice datetime;
Query OK, 241853 rows affected (1.29 sec)
Records: 241853  Duplicates: 0  Warnings: 0

mysql> optimize table test;
+-------------+----------+----------+----------+
| Table       | Op       | Msg_type | Msg_text |
+-------------+----------+----------+----------+
| dbtuna.test | optimize | status   | OK       |
+-------------+----------+----------+----------+
1 row in set (0.00 sec)

N.B. The size of the data file on disk is now 13,931 KB i.e. 945kb larger. This is accounted for by the fact that a datetime column occupies 8 bytes whereas the timestamp column is 4 bytes in size.

This statement was again executed 100 times against the database in order to get a nice rounded average response time, this time the average response time was 0.20 seconds. i.e. 0.11 seconds per execution faster, or in percentage terms 55% faster!

So the conclusion is this... It would appear that for queries which involve date range scans then the datetime column is much much faster.

We also have a DBTuna screenshot which displays the cumulative times for the queries side by side. The first bar shows the time for 100 executions against the timestamp column, the second against the datetime column.

If you'd like find out about the performance of your MySQL Servers 24x7 why not trial dbtuna now? Download Here

Copyright ©2010 DBTuna. All rights reserved.