1

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

  • What is the type of OpenDate? If it is datetime or timestamp, you can simply GROUP BY date(OpenDate) – ypercubeᵀᴹ Dec 19 '17 at 10:48
  • Oh ? Really ? Yes, OpenDate a a DATETIME field. – Joffrey Dec 19 '17 at 10:50
  • Off course. No need for DATE_FORMAT. And what is that crc32() for? Why do you use it? – ypercubeᵀᴹ Dec 19 '17 at 10:50
  • I guess CRC32 is useful for converting value to INT values. So it make the GROUP BY easier for MySQL. I'm not sure because this query has been written by antoher dev. – Joffrey Dec 19 '17 at 10:52
  • The GROUP BY is not easier for integers than dates. Even if it was a bit easier, calling a function (crc32()) as many times as the rows to be grouped (or the rows of the table) is not good. – ypercubeᵀᴹ Dec 19 '17 at 10:59
2

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.

  • Thanks ! So DATE() will use less resources than DATE_FORMAT() ? – Joffrey Dec 19 '17 at 10:58
  • Probably a bit less. Not much but yes, less. Removing the crc32() calls will be another gain. Adding the index much better. How long does the query takes now? – ypercubeᵀᴹ Dec 19 '17 at 11:00
  • OK thank you so much, I'm gonna try it. Now, the query takes up to 60seconds... – Joffrey Dec 19 '17 at 11:03
  • 1
    The index would help, even your existing query. – ypercubeᵀᴹ Dec 19 '17 at 11:08
  • OK thank you. I just another question about index in my case : dba.stackexchange.com/questions/193513/… :-) – Joffrey Dec 19 '17 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.