SHOW INDEX

Grammar Description

SHOW INDEX Returns the index information of the table.

SHOW INDEX returns the following fields:

Field

Description

Table

Table name.

Non_unique

0 if the index does not allow duplicate values; 1 if duplicate values ​​are allowed.

Key_name

Name of the index. If the index is a primary key, the name is always PRIMARY.

Seq_in_index

Sequence number in the index, starting from 1.

Column_name

Column name. See the description of the Expression column.

Collation

How columns are sorted in the index. Possible values ​​are A (ascending), D (descending), or NULL (unsorted).

Cardinality

Estimated number of unique values ​​in the index. To update this value, run ANALYZE TABLE or (for MyISAM table) myisamchk -a.
The cardinality is calculated based on statistical information stored as an integer, so even for small tables, the value is not necessarily accurate. The higher the cardinality, the greater the possibility that MySQL will use indexes when performing a join operation.

Sub_part

Prefix of index. That is, if the column is only partially indexed, it is the indexed number of characters; if the entire column is indexed, it is NULL.
Note: ThePrefix limit is in bytes. However, in the CREATE TABLE, ALTER TABLE, and CREATE INDEX statements, the prefix length used for the index specification is interpreted as the number of characters for non-binary string types (CHAR, VARCHAR, TEXT), and the number of bytes for binary string types (BINARY, VARBINARY, BLOB). Consider using a multibyte character set when specifying the prefix length of a non-binary string column.

Packed

Indicates whether the key is compressed. NULL if not compressed.

Null

YES if the column may contain a NULL value; otherwise, an empty string.

Index_type

Index method used (BTREE, FULLTEXT, HASH, RTREE).

Comment

Other information about the index is not described in its own columns, such as disabled if the index is disabled.

Visible

Whether the index is visible to the optimizer.

Expression

For non-function key sections, Column_name indicates the column indexed by the key section, and Expression is NULL.
For function key sections, the Column_name column is NULL, and Expression indicates the expression of the key section.

Grammar Structure

> SHOW {INDEX | INDEXES}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]

Syntax Description

tbl_name FROM db_name The alternative to the syntax is db_name.tbl_name.

Example

CREATE TABLE show_01(sname varchar(30),id int);
mysql> show INDEX FROM show_01;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Visible | Expression |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| show_01 | 0 | id | 1 | id | A | 0 | NULL | NULL | YES | | | YES | NULL |
| show_01 | 0 | sname | 1 | sname | A | 0 | NULL | NULL | YES | | | YES | NULL |
| show_01 | 0 | __mo_rowid | 1 | __mo_rowid | A | 0 | NULL | NULL | NO | | Physical address | NO | NULL |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.02 sec)