differences between queries to obtain db info?

User, date Message
Written by sergtop
5 years ago
Category: General
56 posts since Mon, 23 Nov 09
what is the difference beetween

show table status from <DB>;

the one that is used on database tab

(SELECT TABLE_NAME AS `Name`, TABLE_TYPE AS `Type`, ENGINE AS `Engine`, VERSION AS `Version`, ROW_FORMAT AS `Row_format`, TABLE_ROWS AS `Rows`, AVG_ROW_LENGTH AS `Avg_row_length`, DATA_LENGTH AS `Data_length`, MAX_DATA_LENGTH AS `Max_data_length`, INDEX_LENGTH AS `Index_length`, DATA_FREE AS `Data_free`, AUTO_INCREMENT AS `Auto_increment`, CREATE_TIME AS `Create_time`, UPDATE_TIME AS `Update_time`, CHECK_TIME AS `Check_time`, TABLE_COLLATION AS `Collation`, CHECKSUM AS `Checksum`, CREATE_OPTIONS AS `Create_options`, TABLE_COMMENT AS `Comment` FROM `information_schema`.TABLES WHERE TABLE_SCHEMA = '<DB>') UNION (SELECT ROUTINE_NAME AS `Name`, ROUTINE_TYPE AS `Type`, NULL AS `Engine`, NULL AS `Version`, NULL AS `Row_format`, NULL AS `Rows`, NULL AS `Avg_row_length`, NULL AS `Data_length`, NULL AS `Max_data_length`, NULL AS `Index_length`, NULL AS `Data_free`, NULL AS `Auto_increment`, CREATED AS `Create_time`, LAST_ALTERED AS `Update_time`, NULL AS `Check_time`, NULL AS `Collation`, NULL AS `Checksum`, NULL AS `Create_options`, ROUTINE_COMMENT AS `Comment` FROM `information_schema`.ROUTINES WHERE ROUTINE_SCHEMA = '<DB>') ORDER BY `Name`;

as the second one works much slower, why is it used?
Written by ansgar
5 years ago
5023 posts since Fri, 07 Apr 06
The second one also reveals stored procedures and functions.

Much slower - should not be the case, at least not reproducible here.

Please login to leave a reply, or register at first.