How big MySQL table should be before breaking it down to multiple tables?

Question

Problem: We have a very big table, and growing. Most of its entries (say 80%) are historical data (with "DATE" field past current date) that are seldom queried, while small part of it (say 20%) are current data ("DATE" field after current date), most queries search these current entries.

Consider two possible scenarios, which one would be better (considering the overall implementation difficulty and performance,...)

1. Breaking the big table into two table: Historical and Current data. And on daily basis I move the records with expired date from Current table to Historical table.

2. Keeping record in one table (the DATA field is defined as INDEXED).

The scenario A would indicate more hustle in implementation and maintenance, and overload on daily bases for moving date between tables, while scenario B would indicate searching a big database (though indexed). Does it impose memory problems? Which scenario is recommended? IS there any other recommendations?

Answer 1

You usually don't want to break a big table into multiple tables, although having a current and historical table is totally reasonable. Your process makes sense. You can then optimize the current table for your query needs. I would probably go for two tables (given the limited information you provide), because it allows such optimization.

However, don't split the historical data. Instead, use partitioning. See the documentation. One caveat: queries need to specify the partitioning key in the where clause to take advantage of the partitions. With a large table, this is typical anyway.

Answer 2

Question: is the historical data necessary for system functionality or are these records stored for other purposes (e.g. audits)? It may be time to clean house by moving the historical data to an archive.