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.
| Type | Time for DATE(col) |
Time for LEFT(col, 10) |
|---|---|---|
| TIMESTAMP | 2.69 | 1.28 |
| DATETIME | 1.67 | 0.48 |
| DATE | 1.63 | 0.39 |
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.
I just learned something new: MySQL has a
BENCHMARK()function specifically for this type of benchmarking.Xaprb
15 Jun 06 at 8:40 pm
Baron,
Got here from your recent post that referenced this one. It may be because LEFT() is just string manipulation, but the DATE() function has to extract the date part — it may have to convert the date to an internal representation to extract the date.
That being said, there should at least be a part of the optimizer that deals with optimizing away something like DATE(col) where col is already in the date format. For instance, SELECT DATE(col) should be optimized to SELECT col when col is in DATE format, so I’d expect that to come back even faster than LEFT().
Sheeri K. Cabral
1 Nov 08 at 4:17 pm
Yes Baron,
You are right that LEFT() is a string function thats reason it is faster then DATE().
Amit Shah
5 Nov 08 at 6:11 am
[...] 雖然上一篇說明本人å好unixtimeçš„æ ¼å¼ã€‚ 但datetimeæ ¼å¼é‚„是很好用的。 æœ€å¤§çš„å„ªé»žå°±æ˜¯åœ¨è³‡æ–™ç« çš„çµæžœä¸ï¼Œå¯ä»¥è¿…速看出到底是幾年幾月幾日。 ä»Šå¤©çœ‹åˆ°é€™ä¸€ç¯‡æ–‡ç« Benchmarks for DATE operations in MySQL [...]
mysqlçš„datetimeæ™‚é–“æ ¼å¼ï¼Œä»¥left增進查詢效率
12 Nov 08 at 9:57 am
I agree with that. LEFT() is faster because DATE has to work with locale and l10n related things that LEFT doesn’t. When using LEFT you are directly saying that the Leftomost part of the string is the date, and is the exact format MySQL is expecting it to be.
Rodrigo
18 Nov 08 at 4:05 pm
OK that’s fine, but to do LEFT() MySQL has first to convert the value from a date to a string — which has all the locale etc overhead too. Seriously, this cannot be anything but a bug.
Xaprb
18 Nov 08 at 6:12 pm