MySQL last table alteration time

Shane Hathaway shane at hathawaymix.org
Wed Mar 28 16:30:48 MDT 2007


Brandon Stout wrote:
> Using a timestamp value for every record to get the table's last updated 
> time is even worse... _every_ record has a timestamp.  When normalizing 
> data, one must also consider what they are using it for.  If you want 
> the last time a record was updated, use a timestamp field in the same 
> table.  If you want the last time a table was updated, use another 
> table.  If you want the last updated record, use another table, and add 
> a RecordID field.

A related concern is that the timestamp table is a hot spot.  If 
concurrent transactions trigger an update to the timestamp table, one of 
the transactions will fail.  One solution is to insert into rather than 
update the timestamp table.  The timestamp table should have no primary 
key, queries of the timestamp table should only consider the most recent 
entry, and there should be a periodic garbage collection of old 
timestamps.  I used a similar solution recently and it has worked 
flawlessly.

Shane




More information about the PLUG mailing list