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?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.