英文:
LEFT vs. DATE_FORMAT in mysql
问题
在SELECT查询中重新格式化日期，是使用LEFT还是DATE_FORMAT更快？我将展示这个问题的示例。
PERSON表信息
| 列名 | 类型 |
|---|---|
| NAME | VARCHAR(20) |
| YEAR | DATETIME |
PERSON表
| NAME | YEAR |
|---|---|
| Travis | 2020-01-01 |
| Sam | 2021-01-01 |
如果执行'SELECT YEAR FROM PERSON'查询，可以看到下面的结果。
| YEAR |
|---|
| 2020-01-01 00:00:00 |
| 2021-01-01 00:00:00 |
但我想要如下结果。
| YEAR |
|---|
| 2020-01-01 |
| 2021-01-01 |
因此，我想要使用以下查询之一。
-
SELECT LEFT(YEAR, 10) FROM PERSON
-
SELECT DATE_FORMAT(YEAR, '%Y-%m-%d')
然而，我想知道哪个查询性能更好。请帮助我。
英文:
LEFT or DATE_FORMAT, which is faster to re-format date in SELECT query in mysql?
I'll show an example of this problem.
Info of PERSON table
| Column name | Type |
|---|---|
| NAME | VARCHAR(20) |
| YEAR | DATETIME |
PERSON
| NAME | YEAR |
|---|---|
| Travis | 2020-01-01 |
| Sam | 2021-01-01 |
If execute 'SELECT YEAR FROM PERSON' query, can see below result.
| YEAR |
|---|
| 2020-01-01 00:00:00 |
| 2021-01-01 00:00:00 |
But I want result like below.
| YEAR |
|---|
| 2020-01-01 |
| 2021-01-01 |
So, I wanted use one of below queries.
-
SELECT LEFT(YEAR,10) FROM PERSON
-
SELECT DATE_FORMAT(YEAR, '%Y-%m-%d')
However, I wonder what query perform better.
Please, help me..
答案1
得分: 0
LEFT() 函数在我的 M1 MacBook 上测试，速度几乎快了四倍。你的结果可能会有所不同。
mysql> select benchmark(100000000, left(year, 10)) from person;
+--------------------------------------+
| benchmark(100000000, left(year, 10)) |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (1.75 sec)
mysql> select benchmark(100000000, date_format(year, '%Y-%m-%d')) from person;
+-----------------------------------------------------+
| benchmark(100000000, date_format(year, '%Y-%m-%d')) |
+-----------------------------------------------------+
| 0 |
+-----------------------------------------------------+
1 row in set (6.81 sec)
但是需要执行这两个表达式一亿次才能观察到显著的差异，它们都非常快，所以我不会担心这个问题。很可能查询的其他部分对性能的影响更大。
担心这两个函数哪个性能更好，就像担心是用一个手指还是两个手指来举起一个100公斤的杠铃。
英文:
Technically, LEFT() is nearly four times faster, based on this test on my M1 Macbook. Your result might vary.
mysql> select benchmark(100000000, left(year, 10)) from person;
+--------------------------------------+
| benchmark(100000000, left(year, 10)) |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (1.75 sec)
mysql> select benchmark(100000000, date_format(year, '%Y-%m-%d')) from person;
+-----------------------------------------------------+
| benchmark(100000000, date_format(year, '%Y-%m-%d')) |
+-----------------------------------------------------+
| 0 |
+-----------------------------------------------------+
1 row in set (6.81 sec)
But given that I had to execute both expressions 100 million times to observe a significant difference, both of them are so fast that I wouldn't worry about it. It's likely that other parts of the query will be of far greater influence on performance.
Worrying about which of these two functions has better performance is like worrying if it's better to use one finger or two fingers to lift a 100kg barbell.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论