set storage_engine = innodb; # rebuild 600 data files in InnoDB. mysql> SELECT count(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; +----------+ | count(*) | +----------+ | 945 | +----------+ 1 row in set (10.14 sec) mysql> SELECT t1.referenced_table_name, t1.referenced_column_name, t0.ORDINAL_POSITION, -> t0.COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, EXTRA, COLUMN_DEFAULT, COLUMN_COMMENT -> FROM INFORMATION_SCHEMA.COLUMNS as t0 -> left outer -> JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS t1 -> ON t1.TABLE_SCHEMA = -> t0.TABLE_SCHEMA AND t1.TABLE_NAME = t0.TABLE_NAME AND t1.COLUMN_NAME = t0.COLUMN_NAME -> WHERE t0.TABLE_SCHEMA = schema() AND t0.TABLE_NAME = 'tt_1' -> ORDER BY t0.TABLE_NAME, t0.ORDINAL_POSITION, t0.COLUMN_NAME, DATA_TYPE, -> CHARACTER_MAXIMUM_LENGTH -> ; Empty set (9.88 sec) # I added temporary timing code to determine how much time was being spent in various # parts of the call stack. These are the relevant results for the joined SELECT above. sql_show.cc; get_all_tables()= 9.850884 sql_show.cc; get_all_tables() for each db loop = 9.824409 sql_show.cc; get_all_tables() for each table loop = 9.812224 sql_show.cc; open_normal_and_derived_tables() = 0.824429 (719 calls) sql_show.cc; get_all_tables()->process_table() = 8.585035 (687 calls) sql_show.cc; get_all_tables()->process_table()-> show_table->file->info(HA_STATUS_VARIABLE|HA_STATUS_NO_LOCK|HA_STATUS_TIME) = 8.506175 (687 calls) ha_innodb.cc; ha_innodb::info() = 8.507651 (1200 calls) ha_innodb.cc; ha_innodb::info()->if (HA_STATUS_TIME) = 8493860 (600 calls) ha_innodb.cc; ha_innodb::info()->if (HA_STATUS_VARIABLE) = 2181 (1200 calls) mysql> set storage_engine = myisam; # rebuild the data files in MyISAM. mysql> SELECT count(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; +----------+ | count(*) | +----------+ | 945 | +----------+ 1 row in set (1.28 sec) mysql> SELECT t1.referenced_table_name, t1.referenced_column_name, t0.ORDINAL_POSITION, -> t0.COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, EXTRA, COLUMN_DEFAULT, COLUMN_COMMENT -> FROM INFORMATION_SCHEMA.COLUMNS as t0 -> left outer -> JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS t1 -> ON t1.TABLE_SCHEMA = -> t0.TABLE_SCHEMA AND t1.TABLE_NAME = t0.TABLE_NAME AND t1.COLUMN_NAME = t0.COLUMN_NAME -> WHERE t0.TABLE_SCHEMA = schema() AND t0.TABLE_NAME = 'tt_1' -> ORDER BY t0.TABLE_NAME, t0.ORDINAL_POSITION, t0.COLUMN_NAME, DATA_TYPE, -> CHARACTER_MAXIMUM_LENGTH -> ; Empty set (1.25 sec) # I added temporary timing code to determine how much time was being spent in various # parts of the call stack. These are the relevant results for the joined SELECT above. sql_show.cc; get_all_tables()= 1.052377 sql_show.cc; get_all_tables() for each db loop = 1.023079 sql_show.cc; get_all_tables() for each table loop = 1.009371 sql_show.cc; open_normal_and_derived_tables() = 0.738829 (719 calls) sql_show.cc; show_table->file->info(HA_STATUS_VARIABLE|HA_STATUS_NO_LOCK|HA_STATUS_TIME) = 0.019789 (687 calls) ha_myisam.cc; ha_myisam::info() = 0.029952 (1269 calls) sql_show.cc; get_all_tables()->process_table() = 0.048722 (687 calls)