My DB design includes multiple MYISAM tables with measurements collected online,
Each row record contains auto-incremented id, some data and an integer representing unixtime.
I am designing an aging mechanism, and i am interested to use MySQL partitioning to partition each such table based on unixtime 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 unixtime is not a unique key, and how would i use unixtime for partitioning purposes)?
- How would i design the partitioning to be fully dynamical based on new records added to the tables?