MySQL SHOW INDEX

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.

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.

Setting up Environment

Here we will take an example of an EMPLOYEE table with EMP_ID, NAME, AGE, and SALARY as columns.

CREATE TABLE EMPLOYEE (EMP_ID INT Primary key,
NAME VARCHAR(20),
SALARY INT,
JOIN_DATE DATE);

Insert data on it:

INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, SALARY) VALUES
(1, 'Sahil', 21, 15000),
(2, 'Alen', 22, 13000),
(3, 'John', 22, 14000),
(4, 'Alex', 20, 13000),
(5, 'Mathew', 22, 14000),
(6, 'Sia', 21, 15000),
(7, 'David', 22, 16000),
(8, 'Tim', 21, 14000),
(9, 'Leo', 20, 15000),
(10, 'Tom', 21, 16000);

Output:

Fig 1. EMPLOYEE Table

Examples of MySQL SHOW INDEX

Example 1: SHOW INDEX of Table When the Database is in Use

Here we will take an example of an EMPLOYEE table to SHOW INDEX

Syntax:

SHOW INDEX from my_table

WHERE [condition];

Query:

SHOW INDEX from EMPLOYEE;

Output:

Fig 2. SHOW INDEX

Explanation: Here we are not using the WHERE clause as it is not compulsory. There are 15 properties of the table displayed when the query is fired. Let’s understand the output.

• Table: The name of the table is an employee.
• Non_unique: 0 as the table cannot contain duplicates.
• Key_name: PRIMARY as the index is primary.
• Seq_in_index: The column sequence number in index starts with 1.
• Column_name: The column name is EMP_ID.
• Collation: ‘A’ as the column is sorted in ascending order.
• Cardinality: There are 10 unique values in the table.
• Sub_part: NULL as the entire column is indexed.
• Packed: NULL as the key is not packed.
• Null: The column cannot contain NULL values.
• Index_type: The BTREE indexing method was used.
• Comment: There were no comments when the index was created.
• Index_comment: There were no comments when the index was created.
• Visible: YES as the index is visible to the optimizer.
• Expression: The Expression is NULL.

Example 2: SHOW INDEX of the Table Using the Database Name

Here we will take an example of an EMPLOYEE table to SHOW INDEX using database name

Syntax:

SHOW INDEX FROM my_table FROM my_db

WHERE [condition];

SHOW INDEX FROM my_db.my_table

WHERE [condition];

There are 2 ways to SHOW INDEX using the database.

Here we will take an example of database_name = ‘sahil‘ and table name ‘employee‘.

Query:

SHOW INDEX FROM EMPLOYEE FROM sahil;

SHOW INDEX FROM sahil.EMPLOYEE;

Output:

Fig 3. SHOW INDEX

Explanation: Here we are using database ‘sahil‘ and the EMPLOYEE table is stored under this database. Here we are not using the WHERE clause as it is not compulsory. Both queries will give the same output. There are 15 properties of the table displayed when the query is fired. Let’s understand the output.

• Table: The name of the table is an employee.
• Non_unique: 0 as the table cannot contain duplicates.
• Key_name: PRIMARY as the index is primary.
• Seq_in_index: The column sequence number in index starts with 1.
• Column_name: The column name is EMP_ID.
• Collation: ‘A’ as the column is sorted in ascending order.
• Cardinality: There are 10 unique values in the table.
• Sub_part: NULL as the entire column is indexed.
• Packed: NULL as the key is not packed.
• Null: The column cannot contain NULL values.
• Index_type: The BTREE indexing method was used.
• Comment: There were no comments when the index was created.
• Index_comment: There were no comments when the index was created.
• Visible: YES as the index is visible to the optimizer.
• Expression: The Expression is NULL.

Conclusion

Finally, the SHOW INDEX command in MySQL offers vital information about database indexing strategy, utilization, and performance. Going through its output enables database administrators and developers to enhance database performance, ensure the integrity of data, and also make well-informed judgments on index maintenance as well as query optimization.