

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- 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?
Advertisements