UnixTime-based MySQL Dynamic Partitioning

Question

My DB project includes several MYISAM tables with measurements collected online,

Each row entry contains an auto-incrementing id, some data, and an integer representing unixtime.

I am developing an aging mechanism, and I am interested in using MySQL partitioning to dynamically partition each such table based on unixtime.

Tell me, what interests me is that each section will represent one month of data, the last section should be 2 months if records are received for the next month not presented, the section that represents 2 months should be reorganized to represent one month, and the new section should be created by representing 2 months (1 taken from the last section and 1 for future measurements)

Also, when a new partition is created, I'm curious that the oldest partition will be deleted.

• What type of partition should I use (my unixtime is not a unique key and how can I use unixtime for splitting purposes)?
• How do I create partitioning completely dynamically based on new records added to tables?

UPDATE 12.12.12

I found an interesting link to a similar approach to what I described your-magical-range-partitioning-maintenance-query .

Answer 1

• Markup should not be based solely on a unique key. However, if a unique key is present, it must be included in the columns used to partition the table. For the partition table in the UNIXTIME column, do:

  ALTER TABLE MyTable PARTITION BY RANGE COLUMNS (UNIX_TIMESTAMP(datetime_column)) ( PARTITION p01 VALUES LESS THAN (2), PARTITION p02 VALUES LESS THAN (3), PARTITION p03 VALUES LESS THAN (4), PARTITION p04 VALUES LESS THAN (MAXVALUE)); 

  Or you can split the datetime column in MySQL 5.5+:

   ALTER TABLE MyTable PARTITION BY RANGE COLUMNS (datetime_column) ( PARTITION p01 VALUES LESS THAN ('2013-01-01'), PARTITION p02 VALUES LESS THAN ('2013-02-01'), PARTITION p03 VALUES LESS THAN ('2013-03-01'), PARTITION p04 VALUES LESS THAN (MAXVALUE)); 

• Fully automated version (each month will be stored in its own section, for 5 months of data storage):

   ALTER TABLE MyTable PARTITION BY RANGE COLUMNS (YEAR(datetime_column)*100 + MONTH(datetime_column)) ( PARTITION p201301 VALUES LESS THAN (201301), PARTITION p201302 VALUES LESS THAN (201302), PARTITION p201303 VALUES LESS THAN (201303), PARTITION p201304 VALUES LESS THAN (201304), PARTITION p201305 VALUES LESS THAN (201305), PARTITION p_MAXVALUE VALUES LESS THAN (MAXVALUE)); DECLARE @Min_Part int DECLARE @Last_Part int DECLARE @SQL varchar (1000) If (select count (distinct MONTH(datetime_column)) from MyTable) > 5 THEN BEGIN select @Min_Part = (select min(year(datetime_column)*100 + month(datetime_column)) from MyTable), @Last_Part = (select max(year(datetime_column)*100 + month(datetime_column)) from MyTable) set @SQL = 'Alter table MyTable REORGANIZE PARTITION p_MAXVALUE (into partition p' +TO_CHAR (@Last_Part) + 'values less than (' + TO_CHAR (@Last_Part) + ')' call common_schema.eval (@sql) set @SQL = 'Alter table MyTable DROP PARTITION p' + TO_CHAR (@Min_Part) call common_schema.eval (@sql) END 

PS Sorry, if SQL is not completely correct - you cannot parse it right now.