The problem with your query is that the grouping is by a function. The database engine needs to compute all of the hour values for each row on the fly, then sort them into buckets. I don't believe that rewriting the query will be particularly helpful for performance tuning.

If you are planning on running this query fairly often, you will probably want to create a stored generated column in the events table (containing the hour of the event). This will allow the database engine to create statistics on the hour of the event, which can be looked up a lot quicker than calculating the result on the fly.

I personally have no experience with MySQL, since I work with SQL Server, so unfortunately I can't provide any code to do the above tasks. But hopefully there's enough key words in there that you can research your own answer.

The problem with your query is that the grouping is by a function. The database engine needs to compute all of the hour values for each row on the fly, then sort them into buckets. I don't believe that rewriting the query will be particularly helpful for performance tuning.

If you are planning on running this query fairly often, you will probably want to create a stored generated column in the events table (containing the hour of the event). Then, indexing this generated column will allow the database engine to create statistics on the hour of the event, which can be looked up a lot quicker than calculating the result on the fly.

I personally have no experience with MySQL, since I work with SQL Server, so unfortunately I can't provide any code to do the above tasks. But hopefully there's enough key words in there that you can research your own answer.