Issue
I have a table with millions of records. I want to fetch the top 10 earners of the current date (calculating by grouping user_id and adding earnings field value of the same user) and I have created the below query for that.
SELECT user_id, SUM(earnings) AS earn
FROM statistics
WHERE created LIKE "2022-03-15%"
GROUP BY user_id
ORDER BY earn DESC
limit 10
The query is giving the expected result, but the problem is that it’s taking around 90 seconds to do processing and all.
Is there any modification possible in the above query to make it faster to get results within 10-15 seconds or less?
Solution
WHERE created LIKE "2022-03-15%"
–>
WHERE created >= "2022-03-15"
AND created < "2022-03-15" + INTERVAL 1 DAY
The former is not "sargable" because it must turn a DATETIME into a string to do the LIKE. The latter is likely to use index idx_created_userid very effectively.
Answered By – Rick James
Answer Checked By – Marie Seifert (BugsFixing Admin)