List all indexes in MySQL

Knowledge-base/databases-queries/MySQL queries

Mars 04th, 2023


How to list all indexes in MySQL database? In this article, you will learn several ways to get a list of all indexes in a MySQL database.

1. Using SQL Query

To start with, we will use an SQL query to get a list of all indexes in the MySQL database. The following SQL script will help you get all indexes:

SELECT table_schema, table_name, index_name, column_name
FROM information_schema.statistics
WHERE non_unique = 1 AND table_schema = "your_database_name";

Sample result

The following is the output that lists all indexes:

list all indexes in MySQL database

If you are looking to list all indexes of a specific table the SQL query to run is as follows.

SELECT table_schema, table_name,  index_name, column_name 

FROM information_schema.statistics  

WHERE non_unique = 1  

            AND table_schema = ‘your_database_name’ 

            AND table_name= ‘your_table_name’, 


2. Using MySQL interactive shell command-line

To get indexes of a table in MySQL database using the MySQL command line, you can use the SHOW INDEXES command line  as follow:

Command:  SHOW INDEXES FROM table_name; Specify the name of the table and it will return all indexes for that table.

For example: Show indexes From film where non_unique= 1;

Result for the table actor: 

list all indexes in MySQL database


3. list all primary keys in MySQL using ERBuilder Data Modeler

ERBuilder Data Modeler allows you to get all details of each table in your MySQL database from columns, constraints, keys, triggers, SQL script, to the list of all indexes. 

Furthermore, with ERBuilder Data Modeler you will have full control over the indexes of a table where you can edit existing ones, create new indexes or delete indexes, just by double-clicking on indexes from the indexes list.

Start modeling your database with us today

Subscribe To Our Newsletter

Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!

Choose a product

Choose a product and get 15 Day fully-functional free trial. No credit card required