MySQL SHOW INDEXES command introduction:
The SHOW INDEXES command can be used as follows to query a table's index data:
You must provide the table name after the FROM keyword to obtain the index of a table: SHOW INDEXES FROM table_name. The query will return details about the index connected to the table in the active database.
If you want to receive the index information for a table in a different database or if you are not linked to any databases at all, you can specify the database name:
Similar to the above question is the following one:
You can substitute these terms in the SHOW INDEXES column since INDEX, KEYS, and IN are synonyms for INDEXES and FROM, respectively. For instance:
Or
The following data is returned by the SHOW INDEXES:
table
The name of the table
non_unique
If the index contains duplicates, the value is 1, otherwise, it is 0.
key_name
the index's title. The word PRIMARY always refers to the primary key index.
seq_in_index
the index's column sequence number. Beginning with 1, the first column's sequence number.
column_name
The column name
collation
The index's column is ordered according to the collation. A stands for ascension, B for descent, or NULL for not sorted.
cardinality
An estimated number of distinct values in the index is returned by the cardinality.
Note that the higher the cardinality, the greater the chance that the query optimizer uses the index for lookups.
sub_part
prefix for the index. If the entire column is indexed, it is null. If the column is only partially indexed, it otherwise displays the total amount of indexed characters.
packed
packed specifies the key's packing; NUL implies that it is not.
null
YES, if the column is capable of holding NULL values, and blank, otherwise.
index_type
The index technique, such as BTREE, HASH, RTREE, or FULLTEXT, is represented by the index_type variable.
comment
Information about the index that isn't covered in its own column, such as disabled if the index is.
index_comment
When you construct an index with the COMMENT feature, index_comment displays the comment associated with that index.
visible
Whether the index is visible or invisible to the query optimizer or not; YES, if it is, NO if not.
expression
If an expression is used as the index value rather than a column or column prefix, the expression serves as the key component expression and the column_name column is NULL.
Filter index information
You may use a WHERE clause to filter index data as follows:
Any data that the SHOW INDEXES statement returns can be used to filter the index data. For instance, the statement that follows only retrieves a table's unseen indexes:
Examples of MySQL SHOW INDEXES
To show the SHOW INDEXES command, we'll create a new table called contacts:
The following command returns all index information from the contacts table:
The output is:
You must add a WHERE clause to the contacts table in order to obtain the invisible indexes:
Here is the output:
You have now learned how to retrieve a table's index information using the MySQL SHOW INDEXES statement.