MySQL Forums
Forum List  »  Performance

Re: Millions of rows, storing aggregate/rollup results help please
Posted by: Rick James
Date: August 30, 2016 02:47PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Millions of rows, storing aggregate/rollup results help please
1106
August 30, 2016 02:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.