Documentation Home
MySQL 9.3 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 261.1Kb
Man Pages (Zip) - 368.3Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.3 Reference Manual  / ...  / INFORMATION_SCHEMA Tables  / INFORMATION_SCHEMA InnoDB Tables  /  The INFORMATION_SCHEMA INNODB_INDEXES Table

28.4.20 The INFORMATION_SCHEMA INNODB_INDEXES Table

TheINNODB_INDEXES table provides metadata aboutInnoDB indexes.

For related usage information and examples, seeSection 17.15.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.

TheINNODB_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 byInnoDB 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 asINNODB_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; 64 = spatial index; 128 = secondary index on avirtual generated column.

  • N_FIELDS

    The number of columns in the index key. ForGEN_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, thePAGE_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 theInnoDBsystem tablespace. Any other number represents a table created with a separate.ibd file infile-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.

  • MERGE_THRESHOLD

    The merge threshold value for index pages. If the amount of data in an index page falls below theMERGE_THRESHOLD value when a row is deleted or when a row is shortened by an update operation,InnoDB attempts to merge the index page with the neighboring index page. The default threshold value is 50%. For more information, seeSection 17.8.11, “Configuring the Merge Threshold for Index Pages”.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE TABLE_ID = 34\G*************************** 1. row ***************************       INDEX_ID: 39           NAME: GEN_CLUST_INDEX       TABLE_ID: 34           TYPE: 1       N_FIELDS: 0        PAGE_NO: 3          SPACE: 23MERGE_THRESHOLD: 50*************************** 2. row ***************************       INDEX_ID: 40           NAME: i1       TABLE_ID: 34           TYPE: 0       N_FIELDS: 1        PAGE_NO: 4          SPACE: 23MERGE_THRESHOLD: 50

Notes

  • You must have thePROCESS privilege to query this table.

  • Use theINFORMATION_SCHEMACOLUMNS table or theSHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.