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:
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:
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!
