The INNODB_SYS_INDEXES
table provides
metadata about InnoDB
indexes, equivalent to
the information in the internal SYS_INDEXES
table in the InnoDB
data dictionary.
For related usage information and examples, see Section 14.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.
The INNODB_SYS_INDEXES
table has
these columns:
INDEX_ID
An identifier for the index. Index identifiers are unique across all the databases in an instance.
NAME
The name of the index. Most indexes created implicitly by
InnoDB
have consistent names but the index names are not necessarily unique. Examples:PRIMARY
for a primary key index,GEN_CLUST_INDEX
for the index representing a primary key when one is not specified, andID_IND
,FOR_IND
, andREF_IND
for foreign key constraints.TABLE_ID
An identifier representing the table associated with the index; the same value as
INNODB_SYS_TABLES.TABLE_ID
.TYPE
A numeric value derived from bit-level information that identifies the index type. 0 = nonunique secondary index; 1 = automatically generated clustered index (
GEN_CLUST_INDEX
); 2 = unique nonclustered index; 3 = clustered index; 32 = full-text indexN_FIELDS
The number of columns in the index key. For
GEN_CLUST_INDEX
indexes, this value is 0 because the index is created using an artificial value rather than a real table column.PAGE_NO
The root page number of the index B-tree. For full-text indexes, the
PAGE_NO
column is unused and set to -1 (FIL_NULL
) because the full-text index is laid out in several B-trees (auxiliary tables).SPACE
An identifier for the tablespace where the index resides. 0 means the
InnoDB
system tablespace. Any other number represents a table created with a separate.ibd
file in file-per-table mode. This identifier stays the same after aTRUNCATE TABLE
statement. Because all indexes for a table reside in the same tablespace as the table, this value is not necessarily unique.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = 74\G
*************************** 1. row ***************************
INDEX_ID: 116
NAME: GEN_CLUST_INDEX
TABLE_ID: 74
TYPE: 1
N_FIELDS: 0
PAGE_NO: 3
SPACE: 60
*************************** 2. row ***************************
INDEX_ID: 117
NAME: i1
TABLE_ID: 74
TYPE: 0
N_FIELDS: 1
PAGE_NO: 4
SPACE: 60
Notes
You must have the
PROCESS
privilege to query this table.Use the
INFORMATION_SCHEMA
COLUMNS
table or theSHOW COLUMNS
statement to view additional information about the columns of this table, including data types and default values.