MySQL Forums
Forum List  »  InnoDB

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

Written By
Snapshot Table -- over the long run
December 11, 2009 03:09PM
December 13, 2009 12:25AM

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.