Mysql - Archiving data, which solution to use?

Question

I am using MySQL 5.x and there is one main table that has the stats combined for all customers. I would like to run reporting on it but dont want to run it on the same table so Im thinking of every night moving the data to a different table that will only be used for reporting. My question is would it be beneficial to have a seperate table for each customer to archive too or just have it just one archive table for all customers? There could be thousands of customers in the system which could mean thousands of archive tables if I decide to break it up by customer. Your thoughts?

Answer 1

If you use the individual for each customer, tables will grow in number.

If you have statistics data then i suggest to summarize it like below

1. keep last 1 month data in the same main table.
2. quartely data/half yearly data in maintable_quartely table that holds the averaged or summarized data based on the report requirements
3. remaing old data in maintable_archieve which holds > quartely or half yearly data averaged or summarized based on the report requirements

If you want the whole thing together you need to combine three tables.

other way, use mysql replication and master for insert,update,delete and slave for select