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 :
- ...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.
DATETIMEis 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.