MySQL Forums
Forum List  »  Data Warehouse

Re: Need advice for table rotation
Posted by: Rick James
Date: July 31, 2012 09:54PM

* Range PARTITION on, say, month. That way, the DELETE will be efficient.

* Create "summary tables". That way, the queries will be efficient.

* Use the smallest practical datatype for each fields. That way, the 'fact' table will not take too much space.

* Use InnoDB. That way, recovery from a power failure will be fast and painless.

* Don't have any indexes other than an AUTO_INCREMENT PRIMARY KEY for the fact table. That way, INSERTs into it will be fast. Periodic augmentation of the summary table(s) can use that to keep track of where they "left off".

* Normalize, but don't over-normalize.

Options: ReplyQuote

Written By
Re: Need advice for table rotation
July 31, 2012 09:54PM

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.