How much irrelevant data should be moved to separate MySQL tables?

Question

MySQL

Colleagues, good afternoon!

1. How reasonable is irrelevant (essentially archival) data to be moved to separate MySQL tables? In my case, these are already completed orders, for example, older than 6 months
2. And if it is reasonable - with a rare need to work with this data, how to get them to calculate the total amount - through UNION?

Answer 1

1 - more likely yes than no
2 - separate table of totals for periods. for the current year - by months, older - by years
2+ - but retrospective control will be required for changes to the "primary data" .. but in general this is all accounting classics

Answer 2

Strongly depends.
Firstly, foreign keys can be assigned to tables, transferring data to another table can "break" the logic.
Secondly, if the table assumes the presence of a really large volume, for example > 1M records, it makes sense to do something, otherwise the transfer to a separate archive table will not bring mega profit.
Well, and most importantly, optimization is basically a science of balance, that is, something must be sacrificed for the sake of speed or data availability.

Answer 3

Here you need to know your motives. Works slowly? Just want to do it right? How many records in 6 months will be in the table? How many table queries per hour? What is the average request processing time?
After all, perhaps everything can be solved with a simple optimization. Perhaps the creation of replicas. It is possible to denormalize the base, or vice versa.