MySQL : DATE_FORMAT() vs. SUBSTR() 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
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.