how to remove 6 months old data automatically from MYSQL innodb table

Question

I have a very big table in which I am fetching data everyday and it's size is getting bigger everyday but for my software, only 6 months data is useful. So, I am planning to remove data which is older than 6 months. I know I can do it using CRON jobs but I want to use MYSQL way to delete older data automatically. I read about PARTITION also and My question is Can I do it using PARTITION or I will have to find some alternative way ?

Answer 1

The Event Scheduler allows to execute regular events according to a schedule. There is detailed example in my post on the Stack Overflow, you just need to change time interval value from 24 hours to 6 months.

Firstly, make sure the Event Scheduler is enabled. To enable it use

SET GLOBAL event_scheduler = ON;

After that you could crate event that will check rows creation time and delete old records. For example

CREATE EVENT cleaning ON SCHEDULE EVERY 1 MONTH ENABLE
  DO 
  DELETE FROM MyTable
  WHERE `timestamp_column` < CURRENT_TIMESTAMP - INTERVAL 6 MONTH;

If there is no column with timestamp of a row creation in your table, then you can create trigger that will insert current timestamp and inserted row identificator to auxiliary table.

CREATE TRIGGER logCreator AFTER INSERT ON MainTable
  FOR EACH ROW 
  INSERT INTO LogTable (MainID, Created) 
  VALUES(NEW.id, CURRENT_TIMESTAMP);

Then you can use this log to get keys of main table rows that was created before specific time and delete corresponding records.

delimiter |
CREATE EVENT cleaning ON SCHEDULE EVERY 1 MONTH ENABLE
  DO
  BEGIN
    DECLARE MaxTime TIMESTAMP;
    SET MaxTime = CURRENT_TIMESTAMP - INTERVAL 6 MONTH;
    DELETE FROM MainTable
    WHERE id IN (SELECT MainID FROM LogTable WHERE Created < MaxTime);
    DELETE FROM LogTable
    WHERE LogTable.Created < MaxTime;
  END |
delimiter ;

Answer 2

PARTITION BY RANGE(TO_DAYS(...)); split by weeks. Then have weekly cron task to drop one week and build a new, empty, partition. Details.

Be sure to adjust the indexes at the time you build the partitions. Any unique or primary key must include the partition key, and that should usually be added at the end.

Provide the main SELECTs and SHOW CREATE TABLE if you would like further critique.

This shows alternatives, such as walking through the PRIMARY KEY values, deleting in chunks. PARTITIONing is the best, if it applies.

Answer 3

That's exactly what I do at present. I partition the table by month, and then at the start of the next month I run a MySQL scheduled event to drop the oldest one, and create a new one. In this way everything is handled internally by MySQL.

Of course it depends on your table structure as to whether or not it can be partitioned in the first place. (plus setting up the partitions initially can take some time depending on the size of the table).

See How to split a MySQL table file?