Re: Need advice for table rotation

[Rick James]

* 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.