Re: Millions of rows, storing aggregate/rollup results help please

[Rick James]

I would consider having the date plus 5 of the columns being the PRIMARY KEY of a summary table, then have all combinations of the other 3 columns (27 more columns if 3 choices each).

Would you care to share your CREATE TABLE and explain the 8 columns if they are not obvious?

More on Summary Tables: http://mysql.rjweb.org/doc.php/summarytables

For keeping the Summary tables up to date:

Plan A (if update frequency is not too high): INSERT ... ON DUPLICATE KEY UPDATE... from the application code.

Plan B (for higher traffic): Gather 10 minute's worth of data, then do them en masse. More on this in http://mysql.rjweb.org/doc.php/staging_table

I hope that your 3-6 values are either normalized or implemented in ENUMs. That way, each is only 1 byte.