MySQL : DATE_FORMAT() vs. SUBSTR()

Question

I'm currently working on MYSQL DB optimizations. I'm looking around some slow queries and I try to optimize some of them.

In my app, for statistics purposes, I'm querying and -very- big table (millions of records) which contains visitors log entries (clicks, opens, impressions,...). That table contains :

• ID (INT, Primary Key)
• OpenDate (DATETIME)
• ListID (INT)
• UserID (INT)
• ...and some other fields

I need to retrieve number of rows per Day. So I need to convert DATETIME into DATE to be able to GROUP BY day value.

• DATETIME is on format yyyy-mm-dd hh:ii:ss
• In the facts, what I need is the only 10 first chars : yyyy-mm-dd

Currently, the query is :

SELECT COUNT(*) AS TotalOpens, DATE_FORMAT(OpenDate,  '%Y-%m-%d') AS DayOfWeek 
FROM `stats_open` WHERE ListID='38' AND UserID='4'
GROUP BY crc32(DayOfWeek) ORDER BY TotalOpens DESC

But I wonder if I should change the DATE_FORMAT function to SUBSTR() function, so MySQL would work a little bit less ? Isnt easier for MySQL to cut a string instead of reformat a date ?

Thanks for your help.

Joffrey

Answer 1

Since OpenDate is a datetime column, you don't need to use DATE_FORMAT(), you can simply GROUP BY DATE(OpenDate). And the extra CRC32() call seem completely redundant.

The query can be rewritten:

SELECT COUNT(*) AS TotalOpens, 
       DATE(OpenDate) AS DayOfWeek 
FROM stats_open 
WHERE ListID = 38 
  AND UserID = 4
GROUP BY DATE(OpenDate) 
ORDER BY TotalOpens DESC ;

Add an index on (ListID, UserID, OpenDate), to make it more efficient.