如何使用 DATE_FORMAT 优化 mysql 组 [英] How to optimize mysql group by with DATE_FORMAT

问题描述

我有一个 sql.

select  count(id) as total, DATE_FORMAT(create_time,"%Y-%m-%d") as create_date
    from  table_name
    group by  DATE_FORMAT(create_time,"%Y-%m-%d");

然后定义列 create_time.

Then Definition of column create_time.

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

据我所知，函数DATE_FORMAT没有带索引，所以我的执行速度很慢.有什么办法可以优化吗?

As far as I know, the function DATE_FORMAT does not take the index, so my execution speed is very slow. Is there any way to optimize it?

mysql 版本:5.6.34

mysql version:5.6.34

推荐答案

您可以创建生成列并在该列上创建索引:

You can create a generated column and create an index on that column:

ALTER TABLE table_name ADD COLUMN create_date DATE AS (DATE(create_time)) VIRTUAL;
CREATE INDEX idx ON table_name(create_date);

由于生成的列是虚拟的，所以不会占用任何空间.(但是，索引当然会使用额外的空间.)然后您可以在查询中使用这个生成的列:

Since the generated column is virtual, it will not use any space. (However, the index will of course use extra space.) You can then use this generated column in your query:

SELECT COUNT(*), create_date FROM t2 GROUP BY create_date;            

这篇关于如何使用 DATE_FORMAT 优化 mysql 组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！