How to get a list of MySQL indexes?

Let us first see how we can display an index from MySQL. For that, use the SHOW command.

The query to show an index is as follows −

mysql> SHOW INDEX FROM indexingdemo;

Here is the output.

+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table        | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| indexingdemo |          1 | indexName |            1 | Name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
1 row in set (0.17 sec)

You can get a list of MySQL indexes with the help of the following query.

Before that, let us first see the syntax.

SELECT DISTINCT
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'yourDatabaseName';

Now, let us apply the above syntax to get the list of MySQL indexes.

mysql> SELECT DISTINCT
   ->     TABLE_NAME,
   ->     INDEX_NAME
   -> FROM INFORMATION_SCHEMA.STATISTICS
   -> WHERE TABLE_SCHEMA = 'business';

The following is the output that displays the list of indexes.

+--------------------------+------------------+
| TABLE_NAME               | INDEX_NAME       |
+--------------------------+------------------+
| addcolumntable           | Name_Index       |
| addingautoincrement      | PRIMARY          |
| addingunique             | name             |
| autoincrement            | PRIMARY          |
| autoincrementtable       | PRIMARY          |
| bookindexes              | BookName         |
| childdemo                | ConstChild       |
| clonestudent             | idIndex          |
| clonestudent             | NameStuIndex     |
| college                  | PRIMARY          |
| compositeprimarykey      | PRIMARY          |
| demoauto                 | PRIMARY          |
| demoindex                | PRIMARY          |
| demoschema               | idDemoIndex      |
| duplicatebookindexes     | BookName         |
| employeeinformation      | PRIMARY          |
| foreigntable             | constFKPK        |
| foreigntabledemo         | FKConst          |
| functionindexdemo        | indFirstName     |
| indexingdemo             | indexName        |
| keydemo                  | PRIMARY          |
| lastinsertrecordiddemo   | PRIMARY          |
| multipleindexdemo        | id               |
| nextiddemo               | PRIMARY          |
| parentdemo               | PRIMARY          |
| primarytable             | PRIMARY          |
| primarytable1            | PRIMARY          |
| primarytabledemo         | PRIMARY          |
| schemadatabasemethoddemo | PRIMARY          |
| sequencedemo             | PRIMARY          |
| student                  | idIndex          |
| student                  | NameStuIndex     |
| studentenrollment        | StudCollegeConst |
| tabledemo2               | ConstFK          |
| tabledemo3               | ConstFK          |
| tablepri                 | PRIMARY          |
| tblf                     | ConstFK          |
| tblp                     | PRIMARY          |
| transcationdemo          | PRIMARY          |
| triggedemo               | PRIMARY          |
| uniqueautoid             | id               |
| uniqueconstdemo          | name             |
| uniquedemo               | name             |
| uniquedemo1              | id               |
| updtable                 | PRIMARY          |
| usernameandpassworddemo  | PRIMARY          |
| usernameandpassworddemo  | UserId           |
+--------------------------+------------------+
47 rows in set (0.07 sec)

George John

Updated on 30-Jul-2019 22:30:23

• Related Questions & Answers
• How to get a list of MySQL views?
• How to get a list of MySQL user hosts?
• How to get a list of MySQL user accounts?
• How to create a MySQL table with indexes?
• How to clone a MySQL table, indexes, and data?
• Get a list of Constraints from MySQL Database?
• Get a list of MySQL databases and version?
• Show/view indexes in a MySQL Database
• Get a list of Foreign Key constraints in MySQL
• Accessing all elements at given Python list of indexes
• How to get the list of tables in default MySQL database?
• How to get a list of installed android Applications?
• How can we get a list of columns in an existing MySQL table?
• How to get length of a list of lists in Python?
• Best practices for using MySQL indexes?