

- 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
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)
- 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?
Advertisements