Mysql – Dynamic MySQL partitioning based on Unix Time


My DB design includes multiple MYISAM tables with measurements collected online,

Each row record contains auto-incremented id, some data and an integer representing Unix time.

I am designing an aging mechanism, and I am interested to use MySQL partitioning to partition each such table based on Unix time dynamically.

Say that I am interested that each partition will represent single month of data, last partition should represent 2 months, if records arrive for the next not represented month, the partition that represented 2 months should be reorganized to represent single month, and new partition should be created representing 2 month (1 taken from the last partition and 1 for future measurements),

Additionally, when a new partition is created i am interested that the oldest partition will be dropped.

I have found and interesting link to similar approach to what i have described your-magical-range-partitioning-maintenance-query.

  1. What type of partitioning I should use (my Unix time is not a unique key, and how would I use Unix time for partitioning purposes)?
  2. How would I design the partitioning to be fully dynamical based on new records added to the tables?