InnoDB INFORMATION_SCHEMA Schema Object Tables In addition to the  SPACE  ID of the tablespace and the  NAME  of the associated table, INNODB_TABLESPACES  provides tablespace  FLAG  data, which is bit level information about tablespace format and storage characteristics. Also provided are tablespace  ROW_FORMAT ,  PAGE_SIZE , and several other tablespace metadata items. The datafile is located in the  test  directory under MySQL’s  data  directory. If a file-per-table tablespace were created in a location outside the MySQL data directory using the  DATA DIRECTORY clause of the  CREATE TABLE  statement, the tablespace  PATH  would be a fully qualified directory path. The  STATS_INITIALIZED  field indicates whether or not statistics have been collected for the table. NUM_ROWS  is the current estimated number of rows in the table. The  CLUST_INDEX_SIZE  and OTHER_INDEX_SIZE  fields report the number of pages on disk that store clustered and secondary indexes for the table, respectively. The  MODIFIED_COUNTER  value shows the number of rows modified by DML operations and cascade operations from foreign keys. The  AUTOINC  value is the next number to be issued for any autoincrement-based operation. There are no autoincrement columns defined on table  t1 , so the value is 0. The  REF_COUNT  value is a counter. When the counter reaches 0, it signifies that the table metadata can be evicted from the table cache. 3311 8. As a final step, insert a row into table  t1  ( TABLE_ID = 71 ) and view the data in the INNODB_TABLESTATS  table. The data in this table is used by the MySQL optimizer to calculate which index to use when querying an  InnoDB  table. This information is derived from in-memory data structures. 7. Using the  SPACE  information from  INNODB_TABLES  once again, query  INNODB_DATAFILES  for the location of the tablespace data file. PAGE_SIZE: 16384  ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single  FS_BLOCK_SIZE: 4096 FILE_SIZE: 114688 ALLOCATED_SIZE: 98304 AUTOEXTEND_SIZE: 0 SERVER_VERSION: 8.0.23  SPACE_VERSION: 1 ENCRYPTION: N STATE: normal mysql>  SELECT * FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE SPACE = 57 \G *************************** 1. row *************************** SPACE: 57  PATH: ./test/t1.ibd mysql>  INSERT INTO t1 VALUES(5, ’abc’, ’def’); Query OK, 1 row affected (0.06 sec) mysql>  SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where TABLE_ID = 71 \G *************************** 1. row *************************** TABLE_ID: 71 NAME: test/t1 STATS_INITIALIZED: Initialized NUM_ROWS: 1  CLUST_INDEX_SIZE: 1  OTHER_INDEX_SIZE: 0  MODIFIED_COUNTER: 1 AUTOINC: 0 REF_COUNT: 1