We are using MySQL where we have almost around 100 tables. Data in these tables grow with time. So we need to delete the older data periodically. I think there are two approaches for this. First is to use delete query in batches and second is to use partitioning. So I had following questions
- Do we really need to use partitioning if we can delete the data in batches of say 5000 records?
- I read that data deletion causes fragmentation, wont partition deletion cause same issue?
- If we use partitioning, Instead of creating partitions frequently, can we create partitions for an year in advance? So that we will have to create partitions only once? (FYI, 20% of the table would have partition of 1 day, 50% would have partition of 7 day and 20-30% would have partition of 1 month)