Make SQL query optimize to return result faster-CodePudding

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 publisher_earn field value of the same user) and I have created the below query for that.

SELECT user_id, SUM(publisher_earn) AS publisher_earnings
FROM statistics
WHERE created LIKE "2022-03-15%"
GROUP BY user_id
ORDER BY publisher_earnings 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?

Table Schema:

CREATE TABLE `statistics` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) unsigned NOT NULL DEFAULT 0,
 `publisher_earn` decimal(50,9) NOT NULL DEFAULT 0.000000000,
 `user_agent` text DEFAULT NULL,
 `created` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_userid` (`user_id`),
 KEY `idx_created_userid` (`created`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=41685 DEFAULT CHARSET=utf8

Note: I am using sum in SQL query because a single user has multiple entries and I want to apply addition on the publisher_earn field.

CodePudding user response：

Is there any modification possible in the above query to make it faster to get results within 10-15 seconds or less?

No, there is nothing you can change in the query that will make up for the lack of indexes on the table. Every time you run that query, MySQL has to look at every single row in the table, which you said is about 8,000,000 rows. Indexes make it so that MySQL only needs to look at the small subset of rows that are relevant.

CodePudding user response：

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.