Benchmarks for DATE operations in MySQLMon, Jun 12, 2006 in Databases
This article compares the relative speed of extracting the date part of a value in MySQL with
LEFT() and with the
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:
I don’t know why it’s faster to use
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.
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.