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 known for its robust and efficient functionality, with user-friendly features and reliability. 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.

What is an Index?

An index in a database is a data structure that improves the speed of data retrieval operations on a table at the cost of additional storage space and increased time to maintain the index during insert and update operations. Common types of indexes include:

• Primary Keys: Unique identifiers for table rows.
• Unique Keys: Ensure that all values in a column are distinct.
• Full-text Indexes: Used for full-text searches.
• Spatial Indexes: Optimized for spatial queries.

Syntax:

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

1. When the database is already in use:

SHOW INDEX from my_table

WHERE [condition];

2. When specifying the database:

SHOW INDEX FROM my_table FROM my_db

WHERE [condition];

3. Another way to specify the database:

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:

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:

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:

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.