Currently, I have a table with approximately 4 million rows, but I'd like to keep only just the last three months of data in the last partition, when this data becomes 91 days, old it should be removed from this partition and send to another partition or table.
For example,
- Partition 0: Just keep the last three months of data in this partition (keep all current data where DateOrder DATE_DIFF (NOW() - INTERVAL 3 MONTHS, NOW()) )
- Partition 1: The rest of the data from the table (keep all the data that is more than 3 months old from the current timestamp. ( DateOrder < NOW() - INTERVAL 3 MONTHS)
CREATE TABLE LargeTable
(
id INT NOT NULL AUTO_INCREMENT
, CustomerName VARCHAR(100)
, CustomerEmail VARCHAR(50)
, DateOrder DATETIME NULL
, PRIMARY KEY (id)
);