How to do view the storage engine for a given MySQL database table?
There are 3 SQL commands you can use with a MySQL client.
- SHOW CREATE TABLE
- INFORMATION_SCHEMA.TABLES
- SHOW TABLE STATUS
SHOW CREATE TABLE
mysql> SHOW CREATE TABLE example_table\G
*************************** 1. row ***************************
Table: example_table
Create Table: CREATE TABLE ` example_table` (
`example_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`version` tinyint(3) unsigned NOT NULL,
`referer` varchar(500) NOT NULL,
`q` varchar(100) DEFAULT NULL,
`uri` varchar(100) DEFAULT NULL,
`pos` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`example_id`)
) ENGINE=MyISAM AUTO_INCREMENT=191226 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
INFORMATION_SCHEMA.TABLES
mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=DATABASE() AND table_name='example_table'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: example_schema
TABLE_NAME: example_table
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 191226
AVG_ROW_LENGTH: 2494
DATA_LENGTH: 477034236
MAX_DATA_LENGTH: 281474976710655
INDEX_LENGTH: 1963008
DATA_FREE: 0
AUTO_INCREMENT: 191227
CREATE_TIME: 2011-01-30 23:40:12
UPDATE_TIME: 2011-01-31 01:06:10
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.00 sec)
SHOW TABLE STATUS
mysql> SHOW TABLE STATUS LIKE 'example_table'\G
*************************** 1. row ***************************
Name: example_table
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 191224
Avg_row_length: 2494
Data_length: 477028992
Max_data_length: 281474976710655
Index_length: 1963008
Data_free: 0
Auto_increment: 191225
Create_time: 2011-01-30 23:40:12
Update_time: 2011-01-31 01:02:45
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
