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.

Our main table, tblLink, will have its primary key as foreign in a few other tables too and one of the tables will be tblMainData which over time will be millions of rows.

We would like to maintain only 3 months of data.

The rest we would like to purge and keep to generate history reports. What is the best mechanism to go about in this scenario?

Should we run a cron job to keep deleting data which is more than 3 months old? If so, how can we store it in a .sql file?

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

As you have foreign keys in your tables so you can't go with partitioning directly.As Partitioning does not support foreign keys in MySQL.

You may go with partitioning but then you will have to create the work around for that.you have to ensure your integrity by using triggers for (INSERT/UPDATE/DELETE) on tables.

i.e Insert into the Child table will only be allowed if the corresponding record exist in Parent table. You have to maintain all these integrity(INSERT/UPDATE/DELETE) by using triggers.

Then you can create the partitioning on table's on a quarter basis and then drop the partition.

for creating the .sql files you may use --where clause with mysqldump it will create the backup in .sql file format.

You can also create the csv's for your old data for the future reference.

share|improve this answer
    
If you need any information other than this please write.. –  Abdul Manaf Jun 23 '12 at 7:25
    
There are going to be quite a number of tables link to the tblLink is which the main table to decide on the trimming. So which is the best mechanism to go about I am kind of lost any experience on your side how to solve this problem? I think I should trim rite rather then just maintain all the old data there? –  newbie14 Jun 23 '12 at 8:51
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.