MySQL Show Indexes

We can get the index information of a table using the Show Indexes statement. This statement can be written as:

mysql> SHOW INDEXES FROM table_name; 

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:

mysql> SHOW INDEXES FROM table_name IN database_name;

The above statement can also be written as:

mysql> SHOW INDEXES FROM database_name.table_name;

 

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:

mysql> SHOW INDEXES IN table_name FROM database_name;  

OR,

mysql> SHOW KEYS FROM table_name IN database_name;

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:

• For functional parts, the expression column represents expression for the key part, and column_name represents NULL.
• For the non-functional part, the expression represents NULL, and column_name represents the column indexed by the key part.

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:

CREATE TABLE `student_info` (  

  `studentid` int NOT NULL AUTO_INCREMENT,  

  `name` varchar(45) DEFAULT NULL,  

  `age` varchar(3) DEFAULT NULL,  

  `mobile` varchar(20) DEFAULT NULL,  

  `email` varchar(25) DEFAULT NULL,  

  PRIMARY KEY (`studentid`),  

  UNIQUE KEY `email_UNIQUE` (`email`)  

)

Next, we create an index on this table by the following command:

mysql> CREATE INDEX mobile ON student_info (mobile) INVISIBLE;  

  

mysql> CREATE INDEX name ON student_info (name) COMMENT 'Student Name';

Now, execute the following command that returns the all index information from the student_info table:

mysql> SHOW INDEXES FROM student_info;  

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:

Mysql> SHOW INDEXES FROM table_name where condition;

Example

If you want to get only invisible indexes of the student_info table, execute the following command:

mysql> SHOW INDEXES FROM student_info WHERE visible = 'NO'; 

It will give the following output: