MySQL 5.6.12 - Truncating OLD data on Large Partitioned Tables (InnoDB)
We have a table with almost 30 million rows and growing, the table is partitioned DATE-wise
We need an efficient way to remove old data, that is quick and does not effect READS or WRITES.
I read from MySQL manuals:
When truncating multiple partitions, the partitions do not have to be contiguous: This can greatly simplify delete operations on partitioned tables that would otherwise require very complex WHERE conditions if done with DELETE statements. For example, this statement deletes all rows from partitions p1 and p3:
ALTER TABLE t1 TRUNCATE PARTITION p1, p3;