I have a table that is constantly growing.
I want to delete rows that are older than 1 year (periodically - each 12 hours)
At first I thought using the ordinary delete statement, but it's not good as there are many entries and the database will get stuck.
Then I read that I can use another approach - moving the "undeleted" entries to a new table, renaiming it and using drop for the old table.
The approach that I wanted to try (and not so sure how to do) is using partitioning.
I want to take my field - created and devide it to months, then each month, delete the same month - a year ago.
example : once a month, 1.1.2016 - > delete all entries from jan 2015.
I removed the primary key and added it as index (as I got error 1503). But still can't figure out how to do it.. can you please advise?
This is the table:
CREATE TABLE `myTable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
`file_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
adding - I tried this :
ALTER TABLE myTable
PARTITION BY RANGE( YEAR(created) )
SUBPARTITION BY HASH( MONTH(created) )
SUBPARTITIONS 12 (
PARTITION january VALUES LESS THAN (2),
PARTITION february VALUES LESS THAN (3),
PARTITION march VALUES LESS THAN (4),
PARTITION april VALUES LESS THAN (5),
PARTITION may VALUES LESS THAN (6),
PARTITION june VALUES LESS THAN (7),
PARTITION july VALUES LESS THAN (8),
PARTITION august VALUES LESS THAN (9),
PARTITION september VALUES LESS THAN (10),
PARTITION october VALUES LESS THAN (11),
PARTITION november VALUES LESS THAN (12),
PARTITION december VALUES LESS THAN (13)
);
but I always get an error : Table has no partition for value 2016 when trying to set created to 2016-01-26 15:37:22
