SHOW INDEX

SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

The SHOW INDEX statement returns information about indexes on a table. To use this statement, you must have some privilege on the table.

The optional EXTENDED keyword includes information about internal indexes that aren’t accessible by users.

You can use the WHERE clause to filter the returned rows using general conditions.

The following table lists the SHOW INDEX output columns.

Column Description
Table The name of the table.
Non_unique Indicates whether the index permits duplicate values: 1 if duplicates are allowed, 0 if the index is unique.
Key_name The name of the index. PRIMARY for primary keys.
Seq_in_index The column sequence number in the index, starting from 1.
Column_name The name of the indexed column. MySQL supports functional key parts, which impacts the column:
  • For a nonfunctional key part, Column_name shows the indexed column.
  • For a functional key part, Column_name is NULL.
Collation Sorting order for the column: A (ascending), D (descending), or NULL (not sorted).
Cardinality An estimate of the number of unique values in the index.
Sub_part The index prefix length if the column is partially indexed, NULL if fully indexed.
Note: Prefix lengths for index specifications are measured in bytes for binary types and in characters for nonbinary string types.
Packed Indicates how the key is packed. NULL if not packed.
Null Indicates whether the column allows NULL values: YES if it does, '' (empty string) if not.
Index_type The index method used (BTREE, FULLTEXT, HASH, or RTREE).
Comment Any additional information about the index, such as if it’s disabled.
Index_comment The comment associated with the index, if any.
Visible Indicates whether the index is visible to the optimizer.
Expression MySQL supports functional key parts, which impacts the column:
  • For a nonfunctional key part, Expression is NULL.
  • For a functional key part, Expression contains the expression for the key part.

Information about table indexes is also available through the INFORMATION_SCHEMA.STATISTICS view.

By default, SHOW INDEX includes a table’s generated invisible key (GIPK), if one exists. You can hide this information by setting the show_gipk_in_create_table_and_information_schema system variable to OFF.

For more information, see the MySQL documentation: SHOW INDEX Statement.