0

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?

1 Answer 1

1

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.

2
  • If you need any information other than this please write.. Jun 23, 2012 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, 2012 at 8:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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