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;
mysql> SHOW INDEXES IN table_name FROM database_name;
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:
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:
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;
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: