How much faster is timestamp than datetime column in MySQL?

Question

This consists of two questions:

1. Is MySQL's timestamp field really faster than datetime field in "order by" query?

2. If the answer to above question is yes, how much faster it could be? Supposed in a table of 100 million rows and frequently sort a bunch of 100-200k rows based on timestamp field inside MySQL, will the sort time improvement be offseted by converting timestamp to readable string format in outside program?

Answer 1

The easiest way to find out is to write a unit test, and actually get some numbers.

My theory was that timestamp would be faster, but according to this blog I am wrong: http://dbscience.blogspot.com/2008/08/can-timestamp-be-slower-than-datetime.html

This is why I tend to get numbers, by profiling, before I decide where to optimize, as my gut feeling can be quite wrong at times.

So, it may depend on the version of MySQL you are using, but it appears that datetime may be faster.

Answer 2

From what I can tell, the major benefit of using TIMESTAMP over DATETIME is being able to automatically set a value to the current time on row creation and being able to set it (or another column with a little effort) to the current time on row update. This allows for an automatic created and modified date.

Because of the other limitations on the TIMESTAMP column (for example, not being to accept dates outside of a certain range or changing if the server time zone changes), DATETIME is going to be preferable if you're not needing one of the two features from above.

However, if you're merely interested in storing a Unix timestamp in the database and not needing the database to ever convert it, you could probably store it as an unsigned integer directly and achieve slightly performance.