MY Sql group by taking too much time [closed]

My sql query is taking 8 sec to run that is worst see my query below. I needed total users count month wise logged in on my server from last year

SELECT `updated_at`, COUNT(`id`) AS `total`  
FROM `table` 
    USE INDEX (`updated_at`) 
WHERE `updated_at` BETWEEN '2017-07-06' AND CURDATE() 
GROUP BY MONTH(`updated_at`), YEAR(`updated_at`) 
ORDER BY id DESC

I also try to explain the query and see what’s going wrong, also used index but nothing work for me.

I have so many queries like this which is taking too many time already spent too much time.

see the explain result below.

Several things you could do:

• add an index on (updated_at), if there isn’t one.

• remove the USE INDEX hint.

• remove updated_at from the select list and ORDER BY id. It makes no sense and would be rejected in recent (5.7+) versions anyway as invalid syntax.

• use ORDER BY YEAR(updated_at), MONTH(updated_at) instead.

• combine the GROUP BY with the ORDER BY.

The new query:

SELECT YEAR(updated_at) AS year,
       MONTH(updated_at) AS month,
       COUNT(*) AS total  
FROM `table`       -- I hope the table has a more sensible name
WHERE updated_at BETWEEN '2017-07-06' AND CURDATE() 
GROUP BY YEAR(updated_at) DESC,
         MONTH(updated_at) DESC
  ;