21.29.8 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table

The INNODB_SYS_INDEXES table provides status information about InnoDB indexes, equivalent to the information from the SYS_INDEXES table in the InnoDB data dictionary.

Table 21.8 INNODB_SYS_INDEXES Columns

Column nameDescription
INDEX_IDAn identifier for each index that is unique across all the databases in an instance.
NAMEThe name of the index. User-created indexes have names in all lowercase. Indexes created implicitly by InnoDB have names in all lowercase. The index names are not necessarily unique. Indexes created implicitly by InnoDB have consistent names: PRIMARY for a primary key index, GEN_CLUST_INDEX for the index representing a primary key when one is not specified, ID_IND, FOR_IND for validating a foreign key constraint, and REF_IND.
TABLE_IDAn identifier representing the table associated with the index; the same value from INNODB_SYS_TABLES.TABLE_ID.
TYPEA numeric identifier signifying the kind of index. 0 = Secondary Index, 1 = Clustered Index, 2 = Unique Index, 3 = Primary Index, 32 = Full-text Index.
N_FIELDSThe number of columns in the index key. For the GEN_CLUST_INDEX indexes, this value is 0 because the index is created using an artificial value rather than a real table column.
PAGE_NOThe root page number of the index B-tree. For full-text indexes, the PAGE_NO field is unused and set to -1 (FIL_NULL) because the full-text index is laid out in several B-trees (auxiliary tables).
SPACEAn identifier for the tablespace where the index resides. 0 means the InnoDB system tablespace. Any other number represents a table created in file-per-table mode with a separate .ibd file. This identifier stays the same after a TRUNCATE TABLE statement. Because all indexes for a table reside in the same tablespace as the table, this value is not necessarily unique.

Notes: