MySQL : DATE_FORMAT() vs. SUBSTR()

date formatMySQLstatistics

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

Best Answer

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.

Related Question