MySql table structure

Question

At the moment, I have a table in mysql that records transactions. These transactions may be updated by users - sometimes never, sometimes often. However, I need to track changes to every field in this table. So, what I have at the moment is a TINYINT(1) field in the table called 'is_deleted', and when a transaction is 'updated' by the user, it simply updates the is_deleted field to 1 for the old record and inserts a brand new record.

This all works well, because I simply have to run the following sql statement to get all current records:

SELECT id, foo, bar, something FROM trans WHERE is_deleted = 0;

However, I am concerned that this table will grow unnecessarily large over time, and I am therefor thinking of actually deleting the old record and 'archiving' it off to another table (trans_deleted) instead. That means that the trans table will only contain 'live' records, therefor making SELECT queries that little bit faster.

It does mean, however, the updating records will take slightly longer, as it will be running 3 queries: 1. INSERT INTO trans_deleted old record; 2. DELETE from trans WHERE id = 5; 3. INSERT INTO trans new records

So, updating records will take a bit more work, but reading will be faster.

Any thoughts on this?

Answer 1

I would suggest a table trans and a *table_revision*

Where trans has the fields id and *current_revision* and revision has the fields id, transid, foo and bar.

To get all current items then:

SELECT r.foo, r.bar FROM trans AS t
  LEFT JOIN revision AS r ON t.id = r.trans_id
  WHERE t.current_revision = r.id

If you now put indexes on r.id and r.trans_id archiving woun't make it much faster for you.

Answer 2

Well typically, you read much more often than you write (and you additionally say that some records may be never changed). So that's one reason to go for the archive table.

There's also another one: You also have to account for programmer time, not only processor time :) If you keep the archived rows in the same table with the live ones, you'll have to remember and take care of that in every single query you perform on that table. Not to speak of future programmers who may have to deal with the table... I'd recommend the archiving approach based on this factor alone, even if there wasn't any speed improvement!