I have an InnoDB MySQL table of this structure:
It contains logs for requests made to one of multiple services. It contains ~3 million rows, and gets ~3 million more rows a month (but I will delete old data if need be).
I am trying to generate a report, and get the number of requests made to each service within a date range.
Here is my current query:
SELECT service, COUNT(*) as lastMonthCount FROM request_logs WHERE datetime > '2021-02-16 10:51:05' GROUP BY service
This works, but is painfully slow (~28 seconds).
It outputs this:
I have indexes on
service. I can see they are of type
How can I radically speed up this query, or restructure my table/indexes so I can achieve the same use case another way?