I have the following query:
SELECT user_id,
type,
cast(created_at as date) AS created_at,
sum(credit) AS credit
FROM wallets_transactions
GROUP BY user_id, type, cast(created_at as date)
It takes about 7 secs to get executed:
Also, I have this index:
wallets_transactions(user_id, type, created_at)
And there is the result of EXPLAIN:
It looks like a full-scan. Any idea how can I make it more optimal?
8
cast(created_at as date) is not sargable .
If created_at is a VARCHAR, that is naughty. Change it to DATETIME There is no problem in comparing a DATETIME to a DATE, so don’t worry about that.
Then INDEX(user_id, type, created_at, credit) would be optimal and “covering”
2