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

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.