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

Published on 30-Nov-2018 11:31:52

Previous Page Print Page