MySQL SHOW INDEX

In MySQL (Relational database management system), the SHOW INDEX function is used to show the index information of the table. It is nothing but the properties of the table. When the show index query is fired on a table it shows 15 properties of the table. Let us take a look at all the properties.

• Table: It shows the table name on which the query is fired.
• Non_unique: It is represented in the form of 0 and 1. 0 indicates that the table cannot contain duplicates and 1 indicates that the table can contain duplicates.
• Key_name: It shows the name of the index. If the index is primary it will show PRIMARY.
• Seq_in_index: It displays the column sequence number in the index, starting with 1.
• Column_name: It displays the column name.
• Collation: It shows how the column is sorted in the index. If the column is sorted in ascending order then it will display ‘A‘, If the column is sorted in descending order then it will display ‘D‘, ‘NULL‘ otherwise.
• Cardinality: It displays the number of unique values in the index.
• Sub_part: It shows the index prefix. i.e. the number of indexed characters if the column is only partly indexed, NULL if the entire column is indexed.
• Packed: It shows how the index is packed. If the index is not packed then it will show NULL.
• Null: It displays YES if the column has NULL values else it will display ‘ ‘.
• Index_type: It shows the index method used. There are generally 4 types BTREE, FULLTEXT, HASH, RTREE.
• Comment:  It displays the information about the index not described in its column, such as disabled if the index is disabled.
• Index_comment: When the index was created, if the COMMENT was given to the index then it will display the COMMENT otherwise it will display ‘ ‘.
• Visible: It will display ‘YES‘ if the index is visible to optimizer, and the ‘NO‘ otherwise.
• Expression: For a nonfunctional key part, Column_name indicates the column indexed by the key part and Expression is NULL. For a functional key part, the Column_name column is NULL and Expression indicates the expression for the key part.

Syntax:

There are 3 ways to write a query to SHOW INDEX in MySQL

When the database is already in use then the syntax will be

SHOW INDEX from my_table

WHERE [condition];

When we want to specify the database then there are 2 syntax

SHOW INDEX FROM my_table FROM my_db

WHERE [condition];

SHOW INDEX FROM my_db.my_table

WHERE [condition];

Explanation: In syntax 1 the database is already in use so we do not need to specify the database. In Syntax 2 and 3 we are specifying the database. It depends on the user whether to use the WHERE clause or not.

MySQL is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995.

MySQL is reputed for its sturdy and quick functioning attributes which involve easy-to-handle features and dependability. MySQL can normally be seen together with dynamic web applications and is generally used to serve languages such as PHP but also other server-side programming languages like Python. In this article, you will discover how the MySQL SHOW INDEX works along with some examples.