MySQL Forums :: Performance :: Millions of rows, storing aggregate/rollup results help please


Advanced Search

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
Millions of rows, storing aggregate/rollup results help please 545 Jacqueline Jacobin 08/28/2016 12:23PM
Re: Millions of rows, storing aggregate/rollup results help please 367 Rick James 08/30/2016 02:47PM
Re: Millions of rows, storing aggregate/rollup results help please 283 Jacqueline Jacobin 08/31/2016 07:03AM
Re: Millions of rows, storing aggregate/rollup results help please 269 Rick James 09/01/2016 04:35PM


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.