14.10.3 Identifying the File Format in Use
If you enable a different file format using the innodb_file_format
configuration option, the change only applies to newly created tables. Also, when you create a new table, the tablespace containing the table is tagged with the “earliest” or “simplest” file format that is required to support the table's features. For example, if you enable the Barracuda
file format, and create a new table that does not use the Dynamic or Compressed row format, the new tablespace that contains the table is tagged as using the Antelope
file format .
It is easy to identify the file format used by a given table. The table uses the Antelope
file format if the row format reported by SHOW TABLE STATUS
is either Compact
or Redundant
. The table uses the Barracuda
file format if the row format reported by SHOW TABLE STATUS
is either Compressed
or Dynamic
.
mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1
Create_time: 2014-11-03 13:32:10
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
You can also identify the file format used by a given table or tablespace using InnoDB
INFORMATION_SCHEMA
tables. For example:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1'\G
*************************** 1. row ***************************
TABLE_ID: 44
NAME: test/t1
FLAG: 1
N_COLS: 6
SPACE: 30
FILE_FORMAT: Antelope
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='test/t1'\G
*************************** 1. row ***************************
SPACE: 30
NAME: test/t1
FLAG: 0
FILE_FORMAT: Antelope
ROW_FORMAT: Compact or Redundant
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0