CODEWITHSUNDEEP
mysqlsqldatabasequery-optimizationsql-date-functions
dai
dai

Reputation: 1075

How to optimize mysql group by with DATE_FORMAT

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

Upvotes: 2

Views: 2726

Answers (3)

Øystein Grøvlen
Øystein Grøvlen

Reputation: 1336

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;

Upvotes: 4

Rick James
Rick James

Reputation: 142288

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.

Upvotes: 0

Bleach
Bleach

Reputation: 561

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

Upvotes: 0

Related Questions