How to Show Indexes for Table or Database in MySQL

1. What Is Index in MySQL
2. Importance of Indexes in MySQL
3. Use SHOW INDEXES to List All Indexes of a Table or Database in MySQL
4. Use SELECT DISTINCT to List All Indexes for All the Tables From a Schema (Database) in MySQL
5. Use STATISTICS Table to List All Indexes for All the Tables From All Schemas (Databases) in MySQL

Today’s article educates on showing indexes for a table or a database in MySQL. We will learn how to get indexes from one or multiple tables within a database or all databases.

We will use SHOW INDEXES, SHOW EXTENDED INDEX, SELECT DISTINCT commands, and the STATISTICS table to get the indexes.

What Is Index in MySQL

A database structure, most likely a B-Tree that we can use to enhance the performance of the database activity, is called an index. A table within a database can have one or multiple indexes associated with that particular table.

Indexes retrieve the required data from multiple columns, boosting the MySQL queries’ efficiency. The specified MySQL query will inspect each row (a record) of a table to get the targeted data if we omit using indexing in MySQL.

Whenever we create a table with a unique or primary key, the special index is automatically created using a field expression named PRIMARY and known as the clustered index.

All indexes excluding the PRIMARY are secondary indexes; we can call them non-clustered indexes.

For instance, we have a students table and an index is created on the ID column. Here, the index will contain a sorted list of the students’ ID values in a table where the list’s every value is accompanied by a reference to the record in the respective table (which is students here) having that value.

The following is a demonstration of what the indexes are.

Importance of Indexes in MySQL

Let’s see the following two visual presentations to observe how the indexed and non-indexed tables are searched using the following query.

SELECT * FROM students WHERE Gender = 'Male'

Searching a Non-indexed Table:

Searching an Indexed Table:

See, the indexes help us retrieve the targeted data only, rather than wandering through all the rows in a table.

Use SHOW INDEXES to List All Indexes of a Table or Database in MySQL

We can use the following queries to get a list of indexes from a specific table or MySQL database.

Show Indexes from a Table:

SHOW INDEXES FROM tableName;

The query above will list all the indexes from the specified table in the current database that you will be using via the use databaseName; query.

If you are not using any database, then the following query can be used to get all the indexes of a table.

SHOW INDEX FROM tableName FROM databaseName;

Alternatively, we can use it in the following way.

SHOW INDEXES FROM tableName IN databaseName;

Remember that the KEYS and INDEX are synonyms of INDEXES; similarly, the IN is a synonym of FROM. Therefore, we can use these synonyms in the SHOW INDEXES.

# The queries below can be used alternatively,
# and produce the same results.
SHOW INDEXES FROM tableName IN databaseName;
SHOW INDEX IN tableName FROM databaseName;

Or

# The queries given below can be used alternatively,
# and produce the same results.
SHOW INDEXES FROM tableName IN databaseName;
SHOW KEYS FROM tableName IN databaseName;

We learned various ways to get a list of indexes from a table that we, as database developers, created. We can use the following query to get all the indexes created by a database developer and the database itself.

SHOW EXTENDED INDEX from databaseName.tableName;

Use SELECT DISTINCT to List All Indexes for All the Tables From a Schema (Database) in MySQL

We can use the following query to get a list of all the indexes for all tables from a particular database. We can use the STATISTICS table in INFORMATION_SCHEMA.

List All the Indexes for All the Tables within a Particular Schema:

# In the following query, you don't have to change anything except
# the value of `TABLE_SCHEMA` to get the table name accompanied
# with all indexes in that table.
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema_name';

Use STATISTICS Table to List All Indexes for All the Tables From All Schemas (Databases) in MySQL

We can use the following query to access all indexes for all the tables from a schema (also called Databases).

USE INFORMATION_SCHEMA;
SELECT * FROM STATISTICS;