MySQL Forums :: InnoDB :: Snapshot Table -- over the long run


Advanced Search

Snapshot Table -- over the long run
Posted by: Fred McGillicuddy ()
Date: December 11, 2009 03:09PM

I have a table with what I consider to be a lot of columns. Some are INT, some are VARCHAR, and a few are TEXT. I want to preserve all previous content (like Wikipedia does), so anytime someone changes the value of a field (or saves the form) it takes a snapshot of the previous data and saves it in the history table.

Is it more efficient, over the long haul, for the history table to be set up like this: ID, main_table_id, main_table_column, value, user_id, time_stamp?

...OR to set it up essentially as a mirror of the main table with an auto incrementing ID column prepended to it? I was hoping to use asynchronous calls, so that anytime a field is blurred it would save JUST the data that was changed, rather than having a lot of duplicate data in the history table. But the history table would necessarily need the value column to be TEXT.

So, which table would be more efficient after a few million inserts and updates?

Options: ReplyQuote


Subject Views Written By Posted
Snapshot Table -- over the long run 3586 Fred McGillicuddy 12/11/2009 03:09PM
Re: Snapshot Table -- over the long run 1662 Rick James 12/13/2009 12:25AM
Re: Snapshot Table -- over the long run 1446 Peter Brawley 12/13/2009 11:29AM


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.