Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

  1. What type of partitioning i should use (my unixtime is not a unique key, and how would i use unixtime for partitioning purposes)?
  2. How would i design the partitioning to be fully dynamical based on new records added to the tables?
share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.