Difference between SHOW INDEX, SHOW INDEXES and SHOW KEYS in MySQL?

There is no difference between show index, show indexes and show keys. They have similar meaning.

Let us first create a table −

mysql> create table DemoTable1549
   -> (
   -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> EmployeeName varchar(20)
   -> );
Query OK, 0 rows affected (0.82 sec)

Following is the query to create an index −

mysql> create index name_index1 on DemoTable1549(EmployeeName);
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

Following is the query for SHOW INDEX −

mysql> show index from DemoTable1549;

This will produce the following output −

+---------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table         | Non_unique | Key_name    | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+---------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| demotable1549 |          0 | PRIMARY     |            1 | EmployeeId   |         A |           0 |     NULL |   NULL |      | BTREE      |         |               |     YES |
| demotable1549 |          1 | name_index1 |            1 | EmployeeName |         A |           0 |     NULL |   NULL |  YES | BTREE      |         |               |     YES |
+---------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.17 sec)

Here is the query for SHOW INDEXES −

mysql> show indexes from DemoTable1549;

This will produce the following output −

+---------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table         | Non_unique | Key_name    | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+---------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| demotable1549 |          0 | PRIMARY     |            1 | EmployeeId   |         A |           0 |     NULL |   NULL |      | BTREE      |         |               |     YES |
| demotable1549 |          1 | name_index1 |            1 | EmployeeName |         A |           0 |     NULL |   NULL | YES  | BTREE      |         |               |     YES |
+---------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.00 sec)

Here is the query to implement SHOW KEYS −

mysql> show keys from DemoTable1549;

This will produce the following output −

+---------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table         | Non_unique | Key_name    | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+---------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| demotable1549 |          0 | PRIMARY     |            1 | EmployeeId   |         A |           0 |     NULL | NULL   |      | BTREE      |         |               |     YES |
| demotable1549 |          1 | name_index1 |            1 | EmployeeName |         A |           0 |     NULL | NULL   | YES  | BTREE      |         |               |     YES |
+---------------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.00 sec)

AmitDiwan

Updated on 12-Dec-2019 06:21:48

• Related Questions & Answers
• MongoDB difference between show dbs and show databases?
• Show/view indexes in a MySQL Database
• Only show tables with certain patterns in MySQL “show tables”?
• Show Bootstrap class
• Group result in MySQL and show on list?
• Why SHOW DBS does not show my databases in MongoDB?
• Show MySQL host via SQL Command?
• Return order of MySQL SHOW COLUMNS?
• How to show that each MySQL enumeration has an index value?
• How to customize `show processlist` in MySQL?
• Show constraints on table command in MySQL?
• Show column value twice in MySQL Select?
• Show content with Bootstrap
• jQuery Effect show() Method
• MySQL show tables sort by table name?