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)