When was my table last ALTERed?

Question

I'm using mysql 5.1.41-3ubuntu12.10 and would like to know when my table was last ALTERed (or CREATEd, if it was never ALTERed).

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA();
gives the CREATE and last UPDATE time, but not AFAICT the last ALTER time.

Answer 1

The answer depends somewhat on the storage engine. The most reliable indicator of when the table was last altered is to look at the modified time on the .frm file in the data directory. That file should be updated every time you alter the table, even for changes like updating a column default that don't require a table rebuild.

information_schema.tables.create_time is a bit of a misnomer, since that value actually changes most of the time when you alter a table. However, this is one area where the storage engine is relevant. If you do an alter without a rebuild (like changing a column default value) in InnoDB then information_schema.tables.create_time is updated, but if you do the same in MyISAM information_schema.tables.create_time is not updated. In both cases the .frm file should be updated, so I'd recommend you check the file timestamp for the most accurate data if you have access to it.