Quote
CAST( DATE_FORMAT( created_at, '%Y-%m-%d %k:%i:00' ) AS DATETIME )
This expression takes a
Date value, uses the Date_Format function to change it into a given, Character "shape", then casts the result
back into a
DateTime. Being a function call, it's going to do this for each and every record, which probably explains the performance problems.
I
think it's trying to get a grouping down to Minute level. I would imagine there's a better way to do so.
At the very least, lose the cast to DateTime and use the "seconds-truncated" value in its character form. That should
reduce the load - casting
to a Date is generally slower than formatting
from one - but it's still not going to be brilliant.
Air-code follows:
You might be better off extracting the raw data and then grouping it in a second step:
SELECT
CAST( MinuteResolved AS DATETIME ) AS `Date`
, COUNT( * ) Count
FROM
(
SELECT
DATE_FORMAT( created_at, '%Y-%m-%d %k:%i:00' ) AS MinuteResolved
FROM
dbSupaLeads_log.v2_leads_request_response
WHERE
created_at BETWEEN NOW() - INTERVAL 5 HOUR AND NOW()
AND status = 'Accepted'
) T0
GROUP BY MinuteResolved
ORDER BY MinuteResolved ;
Regards, Phill W.