Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.4Mb
PDF (A4) - 31.4Mb
PDF (RPM) - 29.7Mb
HTML Download (TGZ) - 7.4Mb
HTML Download (Zip) - 7.4Mb
HTML Download (RPM) - 6.3Mb
Man Pages (TGZ) - 179.1Kb
Man Pages (Zip) - 289.5Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table

21.30.21 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table

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, and ID_IND, FOR_IND, and REF_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 index

  • N_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 a TRUNCATE 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 the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.