MySQL Forums
Forum List  »  Merge Storage Engine

Design Advice for archiving
Posted by: F Simons
Date: January 19, 2011 09:52PM

Hi guys, can you please advise with some design advice with the following situation: We have a 1 - 6 million record table where only 5000 records are actively updates. The rest of the records are read only.

We're thinking of archiving these records to another table and using merge tables. ie to take advantage of faster optimizes on the active table, and possible target indexes.

I'm also concerned about performing the archive. ie 1. insert/select from active table to archive table and then delete the copied records from the active table. I've done some profiling and the delete is a bottleneck when the volume is high. I've extended my design (which I explain below) to manage active 2 tables which I flip so I can truncate an entire table when archiving.

Is the following a good solution for archiving records and should I be using merge tables?

- active1
- active2
- archive
- mergetable (merge table union=(archive, active1, active2), insert_method=last

Archive Process (assuming target table is active2):
1. lock tables
2. Copy records to archive from active1 to archive
3. flush tables
4. Truncate table active1
5. flush tables
6. set union=(archive, active2, active1). ie flip active1 and active2 so active1 is now the active table

When the archive process is run the next time, the logic is flipped to consider active 1 as the target table. ie data is copied from active2 and active2 is truncated.

Am I on the right track with this guys?

Options: ReplyQuote

Written By
Design Advice for archiving
January 19, 2011 09:52PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.