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.
updated_atfrom the select list and
ORDER BY id. It makes no sense and would be rejected in recent (5.7+) versions anyway as invalid syntax.
ORDER BY YEAR(updated_at), MONTH(updated_at)instead.
GROUP BYwith the
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 ;