MySQL इंडेक्स की सूची कैसे प्राप्त करें?

MySQL में इंडेक्स देखने के दो तरीके

विधि 1: SHOW INDEX (सिंगल टेबल के लिए)

mysql> SHOW INDEX FROM indexingdemo;

आउटपुट:

+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| 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     |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

विधि 2: INFORMATION_SCHEMA.STATISTICS (सभी टेबल के लिए सूची)

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

उदाहरण: 'business' डेटाबेस

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

नमूना आउटपुट (आंशिक):

+--------------------------+------------------+
| TABLE_NAME               | INDEX_NAME       |
+--------------------------+------------------+
| addcolumntable           | Name_Index       |
| addingautoincrement      | PRIMARY          |
| addingunique             | name             |
| autoincrement            | PRIMARY          |
| bookindexes              | BookName         |
| childdemo                | ConstChild       |
| clonestudent             | idIndex          |
| clonestudent             | NameStuIndex     |
| college                  | PRIMARY          |
| compositeprimarykey      | PRIMARY          |
| ...                      | ...              |
+--------------------------+------------------+

उपयोगी विविधताएं

केवल नॉन-यूनिक इंडेक्स

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'db_name' AND NON_UNIQUE = 1;

किसी विशिष्ट टेबल के सभी इंडेक्स विवरण

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'table_name';

महत्वपूर्ण कॉलम