[SOLVED] Make SQL query optimize to return result faster

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)

Leave a Reply

Your email address will not be published.