Can a timestamp be slower than a datetime?

One should use timestamps rather than datetimes as timestamps are four bytes and datetimes are eight bytes, right?  This makes sense as smaller indexes and tables are faster than larger ones. However, when I was trying to quantify the improvement (I’m strange that way), I found the exact opposite - when used as a criteria, a timestamp is slower than a datetime. Often much slower.

For example, for this simple sql, when purchaseDate was a timestamp, the query took about 9.4 seconds and the datetime version took about 4.3 seconds. In this case the data was in memory.

    select sum(unit) from Sale where purchaseDate >= '2001-07-01' and purchaseDate < '2001-08-01'

I did other tests, such as when data wasn’t cached and there was io, and I found cases where datetimes were much faster than timestamps, differences so extreme I’m hesitant to report them until I have a better explanation of what is going on. I really can’t believe the performance differences I'm measuring and I have this suspicion that something else is occurring as these numbers seem too extreme, but what I can’t say.

Was there a bug for this I don’t know about? It is hard to find information on timestamps as it is such a common word, but I did scan the MySQL bugs for timestamps and didn’t find anything. Is it possible the extra functionality of timestamps, like timezone conversions, makes timestamps slower than datetimes?

In a few other cases, I found them about the same speed; only in one case were timestamps faster than datetimes, and that was 94 seconds for timestamps and 97 seconds for datetimes, which is an insignificant difference.

I tested this on 5.1.26 and 5.0.22, and found the performance difference between timestamps and datetimes on both versions.

I'm not sure what to conclude from this. If you aren’t frequently using the date oriented columns as criteria in queries, and dates are something like a rarely used record of when an event occurred, the smaller timestamp is the better choice. If, however, if many queries are run against the date in question, and assuming these results I’m getting are accurate, then a datetime column should at least be under consideration. In other words, benchmark datetimes and timestamps in your environment with representative queries. The results are worse for timestamps when they are used as a selection criteria.

If you get the crazy performance differences I’m getting, or if you don’t, let me know.

 

For the curious, the plans for the timestamp queries looked like this:

mysql> explain select sum(unit) from SaleT where purchaseDate >= '2001-07-01'  and purchaseDate < '2001-08-01' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: SaleT
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 20267520
        Extra: Using where
1 row in set (0.04 sec)

And the datetime queries looked like this:

mysql> explain select sum(unit) from Sale where purchaseDate >= '2001-07-01'  and purchaseDate < '2001-08-01' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Sale
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 20548080
        Extra: Using where
1 row in set (0.06 sec)

And the table looks like this (with the other version having a purchaseDate timestamp column instead of a datetime).

 

CREATE TABLE  `test`.`Sale` (
  `orderId` int(11) NOT NULL,
  `customerId` int(11) NOT NULL,
  `productId` int(11) NOT NULL,
  `productBigId` int(11) NOT NULL,
  `unit` int(11) NOT NULL,
  `purchaseAmount` decimal(16,2) NOT NULL,
  `purchaseCost` decimal(16,2) NOT NULL,
  `purchaseDate` datetime NOT NULL,
  PRIMARY KEY  (`purchaseDate`,`orderId`),
  UNIQUE KEY `idx_sale_order` (`orderId`),
  KEY `pf_sale_product` (`productId`),
  KEY `pf_sale_customer` (`customerId`),
  CONSTRAINT `pf_sale_customer` FOREIGN KEY (`customerId`) REFERENCES `Customer` (`customerId`),
  CONSTRAINT `pf_sale_product` FOREIGN KEY (`productId`) REFERENCES `Product` (`productId`)
) ENGINE=InnoDB

 

On a somewhat related note, I also got this repeatable bug when trying to insert data into a timestamp column on 5.1.26:

    Incorrect datetime value: '2001-03-11 02:00:39' for column 'purchaseDate' at row 226