MySQL Index: Show Index

In this tutorial, we will see how we can view the index of a table or a database using the SHOW INDEX command and we’ll also learn how to see all the indexes present in a particular database.

MySQL Indexes are the data structure used for improving the performance of search results. Indexes speed up the process of searching on a database that is having thousands of records, as it does not iterate through each and every element, instead, it only iterates the index which helps in reducing the computational time of a search.

View Indexes in MySQL

We can perform multiple operations on a SQL table with respect to Index, we can create an index, we can drop an index, and view the index as well. In this section, we’ll see different ways through which we can view an index and we’ll also see the syntax.

View Indexes of a particular table

When we want to view the index or indexes of a particular table we use the SHOW INDEX command, this command shows all the indexes present for that particular table that is mentioned in the query.

The syntax for the SHOW INDEX command:

SHOW INDEX FROM table_name;Code language: SQL (Structured Query Language) (sql)

View all the Indexes present in a database

We can also view indexes for a particular database, we use SELECT for the same. It shows all the indexes of different tables present in a particular database.

The syntax for viewing all the indexes present in a database:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'database_name';Code language: SQL (Structured Query Language) (sql)

View all the Indexes present in a server

We can see all the indexes present in all the databases as well, we can simply remove the WHERE clause in the above command to do so.

The syntax for viewing the entire index list:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS;Code language: SQL (Structured Query Language) (sql)

Simple Examples to view Index

Let’s take a few examples of show indexes for a better understanding. Let’s assume we have a table called marks, and we want to see all the indexes associated with the table. We can use the below query for the same:

SHOW INDEX FROM marks;Code language: SQL (Structured Query Language) (sql)

The above command will show all the indexes related to the marks table.

Let’s take another example where we want to get all the indexes from a particular database, this means that regardless of how many tables are present in the database, the query will fetch all the indexes corresponding to each and every table present in that particular database. In this example, we’ll have a database named school_management that contains multiple tables like the student table, staff table, fees table, salary table, etc.

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'school_management';Code language: SQL (Structured Query Language) (sql)

Let’s take the last example, when we want to fetch all the indexes present in a server, we use the below command:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS;Code language: SQL (Structured Query Language) (sql)

The above command will retreat all the indexes present in the server, a glimpse of the output can be seen in the below screenshot. This command will also show all the inbuilt indexes as well.

Conclusion

This tutorial focused on all the aspects of the show index command. We had a brief introduction about what an index actually is and how to view indexes. We saw the SHOW INDEX command to see indexes for a particular table, we also learned how to view all the indexes of a database and how we can see all the inbuilt indexes as well.

Also See: The official Document to know more about MySQL Index.