Creation time of Innodb tables

Question

CREATE_TIME column of "TABLES" table from INFORMATION_SCHEMA shows the same CREATE_TIME for all my InnoDB tables. It means all these tables were created between 2010-03-26 06:52:00 and 2010-03-26 06:53:00 while actually they were created a few months ago.

Does the CREATE_TABLE field change automatically for InnoDB tables?

Answer 1

For InnoDB, the CREATE_TIME value in INFORMATION_SCHEMA.TABLES is based on the modified time of the table's FRM file. So this will most likely represent the last time you ran ALTER TABLE or OPTIMIZE TABLE.

Answer 2

The create_time and update_time fields of information_schema correspond to the creation/modification timestamps of the underlying storage for the table.

With MyISAM, each table has its own file, so the create/modify timestamp of that file is returned.

For InnoDB however, storage for all tables is inside a single file, ibdata, so there is only one create/modify timestamp. This one timestamp is returned for all InnoDB tables.