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?
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)