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.
- 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)?
- How would I design the partitioning to be fully dynamical based on new records added to the tables?