Benchmarks for DATE operations in MySQL

This article compares the relative speed of extracting the date part of a value in MySQL with LEFT() and with the DATE() function.

LEFT() is faster than DATE(). To prove this, I inserted two million un-indexed sequential values into a table and selected the minimum and maximum values. Both queries are table scans, so it does read through all the records. The table below lists the time in seconds for MAX() on my computer. I tested with three data types: DATE, TIMESTAMP and DATETIME.

I don’t know why it’s faster to use LEFT() than DATE(). I would assume the reverse to be true, but clearly it’s not, at least on the systems I’ve tested.

The time for MIN() is one or two milliseconds faster than MAX(), probably because the values are sequential and only one assignment is performed, whereas the MAX() query must perform two million assignments.

See Also

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.