Mysql – Optimizing a DB Query

MySQLmysql-5.5

I am using MySQL database with PHP's Yii Framework, It is an old version of MySQL, It can't be updated to latest one due to some reasons.

I have created this query to get Current month records of current year. This query was working fine on my local system, I have around 10,000 records, But when i made it to production server, it was also working fine but after 1 month, Records reach upto 1 million in that table, So query is taking more than 10 seconds, when there is huge traffic on the app, its makes the server slow, mysql is taking high CPU. But when records are less, The query is working fine, Otherwise query is taking so much time to return records, sometimes it takes like 25-26 seconds.

Here is my query

SELECT SUM(`coins`) as coins, subscription_id 
FROM `user_rewards` `t` 
WHERE YEAR(date_awarded) = YEAR(CURRENT_DATE) 
  AND MONTH(date_awarded) = MONTH(CURRENT_DATE) 
GROUP BY subscription_id 
ORDER BY coins DESC LIMIT 10

Is it possible to optimize this query more in a better way?

This is my user_rewards table

id, subscription_id, coins, date_awarded

A single subscription_id can have multiple records thats why I sum and do group by in my group…

Below is table schema.

CREATE TABLE `user_rewards` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date_awarded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `subscription_id` bigint(20) NOT NULL,
 `coins` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_sub_id` (`subscription_id`),
 CONSTRAINT `fk_sub_id` FOREIGN KEY (`subscription_id`) REFERENCES `subscription_info` (`subscription_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12907 DEFAULT CHARSET=latin1

Best Answer

First visible improvement (assuming that there is no date_awarded values in future) is

WHERE date_awarded >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

Second - the index by (subscription_id, date_awarded, coins) (or maybe (date_awarded, subscription_id, coins), depends on table statistic, must be tested).

Related Question