Thursday, March 23, 2017

Identity Table Storage Engine

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)

References