MySQL Show Indexes
We can get the index information of a table using the Show Indexes statement. This statement can be written as:
In the above syntax, we can see that if we want to get the index of a table, it requires to specify the table_name after the FROM keyword. After the successful execution of the statement, it will return the index information of a table in the current database.
If we want to get the index information of a table in a different database or database to which you are not connected, MySQL allows us to specify the database name with the Show Indexes statement. The following statement explains it more clearly:
The above statement can also be written as:
Note: It is noted that Index and Keys both are synonyms of Indexes, and IN is the synonyms of FROM keyword. Therefore, we can also write the Show Indexes statement with these synonyms as below:
The SHOW INDEX query returns the following fields/information:
Table: It contains the name of the table.
Non_unique: It returns 1 if the index contains duplicates. Otherwise, it returns 0.
Key_name: It is the name of an index. If the table contains a primary key, the index name is always PRIMARY.
Seq_in_index: It is the sequence number of the column in the index that starts from 1.
Column_name: It contains the name of a column.
Collation: It gives information about how the column is sorted in the index. It contains values where A represents ascending, D represents descending, and Null represents not sorted.
Cardinality: It gives an estimated number of unique values in the index table where the higher cardinality represents a greater chance of using indexes by MySQL.
Sub_part: It is a prefix of the index. It has a NULL value if all the column of the table is indexed. When the column is partially indexed, it will return the number of indexed characters.
Packed: It tells how the key is packed. Otherwise, it returns NULL.
NULL: It contains blank if the column does not have NULL value; otherwise, it returns YES.
Index_type: It contains the name of the index method like BTREE, HASH, RTREE, FULLTEXT, etc.
Comment: It contains the index information when they are not described in its column. For example, when the index is disabled, it returns disabled.
Index_column: When you create an index with comment attributes, it contains the comment for the specified index.
Visible: It contains YES if the index is visible to the query optimizer, and if not, it contains NO.
Expression: MySQL 8.0 supports functional key parts that affect both expression and column_name columns. We can understand it more clearly with the below points:
MySQL SHOW INDEX Example
Here, we are going to create a table student_info that contains the student id, name, age, mobile number, and email details. Execute the following command to create a table:
Next, we create an index on this table by the following command:
Now, execute the following command that returns the all index information from the student_info table:
We will get the output below:
Filter Index Information
We can filter the index information using where clause. The following statement can be used to filter the index information:
If you want to get only invisible indexes of the student_info table, execute the following command:
It will give the following output: