i remember being surprised by how slow GROUP BY year(date), month(date) was in MySQL, despite the date column being properly indexed.
the solution was to store each of those in separate indexed columns instead of re-computing them at query time. think i set up an INSERT trigger to add these to a separate "index" table that i then joined to get the GROUP BY to be fast.
That concept was a big part of the data modeling story when I was helping people understand how to use Riak, a distributed key/value store. Generate your results as you receive the data, whenever possible, and run batch processes to generate them when you can’t.
MySQL has supported index expressions for nearly four years, since MySQL 8.0.13.
Prior to that, you could accomplish the same thing indirectly by creating a virtual column with the desired expression, and then creating an index over that virtual column. This has been supported for six years, since MySQL 5.7. So at this point all non-EOL versions of MySQL support this.
the solution was to store each of those in separate indexed columns instead of re-computing them at query time. think i set up an INSERT trigger to add these to a separate "index" table that i then joined to get the GROUP BY to be fast.