Innodb Tables Creation Time

Question

Column CREATE_TIME of table "TABLES" from INFORMATION_SCHEMA shows the same CREATE_TIME for all my InnoDB tables. This means that all these tables were created between 2010-03-26 06:52:00 and 2010-03-26 06:53:00, while in fact they were created several months ago.

Does CREATE_TABLE automatically change for InnoDB tables?

Answer 1

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

Answer 2

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

In MyISAM, each table has its own file, so the timestamp for creating / modifying this file is returned.

However, for InnoDB, the repository for all tables is inside the same ibdata file, so there is only one create / change timestamp. This timestamp is returned for all InnoDB tables.

Answer 3

I used MySQL 5.7.17.

If you have a MyISAM table, it will show you the correct date of the created table, even if you have an Optimized MyISAM table. (Show table status, where name = 'tablename;)

if you have innodb tables, it will show you the incorrectly created date, because after the internal optimization of the innodb table, it will use the alter table and therefore it will show you the date and time of the created table, which will be after the optimization date (Optimize table table name;)

For this permission, we need to use information_schema.tables to get the date the table was created.