How to optimize mysql group by with DATE_FORMAT

Question

I have a 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");

Then Definition of column create_time.

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

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 version:5.6.34

I don't think there is a way to optimize this in MySQL. I am curious if anyone knows a trick. — Gordon Linoff, Jun 20 '18 at 11:10
@GordonLinoff If we can't optimize, then how do we solve the slow query in this case? — dai, Jun 21 '18 at 01:56

score 3 · Accepted Answer · answered Jun 22 '18 at 08:21

3

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;

answered Jun 22 '18 at 08:21

Øystein Grøvlen

1,266
6
8

Does mysql5.6 support Virtual Generated Column? – dai Jun 22 '18 at 08:31
No, you need MySQL 5.7 – Øystein Grøvlen Jun 22 '18 at 08:49
Thanks for your suggestion, but I can't upgrade mysql – dai Jun 22 '18 at 08:53
Does the query increase the value of the status variable "Created_tmp_disk_tables"? If so, it may help to increase the size of both max_heap_table_size and tmp_table_size. – Øystein Grøvlen Jun 22 '18 at 08:53
Will this increase query speed? – dai Jun 22 '18 at 09:01
A temporary table will be used to store the counts for each group. If a an in-memory table can be used for this, it should be faster than if an ordinary MyISAM table is used. – Øystein Grøvlen Jun 22 '18 at 09:19
I don't know the concept of a temporary table, I need a little time to understand – dai Jun 22 '18 at 09:39
I understand your answer, I think it is a way to improve performance, but I want other ways, such as sql itself – dai Jun 22 '18 at 10:45

score 0 · Answer 2 · answered Jun 20 '18 at 03:34

0

I would try taking LEFT(create_time,10), that should utilize the index.

answered Jun 20 '18 at 03:34

Bleach

561
4
11

score 0 · Answer 3 · answered Jun 20 '18 at 05:26

0

This won't speed it up, but it is cleaner:

select  count(*) as total,
        DATE(create_time) as create_date
    from  table_name
    group by  DATE(create_time);

COUNT(id) checks id for being NOT NULL; that is probably unnecessary.

INDEX(create_time) will help some, but all it will do is turn a "table scan" into an "index scan".

If this is a Data Warehouse application (zillions of write-once rows, lots of "reports"), then we can discuss ways to make queries like this run orders of magnitude faster.

answered Jun 20 '18 at 05:26

Rick James

135,179
13
127
222

Yes, there is a large amount of data to be counted. – dai Jun 20 '18 at 05:33
@Frank.Dai - Would it work to get subtotals each night? Then the 'report' query will be very fast. More: http://mysql.rjweb.org/doc.php/summarytables – Rick James Jun 20 '18 at 14:10

How to optimize mysql group by with DATE_FORMAT

3 Answers3

Linked