How can I view the indexes I have set up in MySQL?

To view the indexes, you can use SHOW command.

Following is the syntax −

show index from yourTableName;

Let us first create a table −

mysql> create table DemoTable
   (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   FirstName varchar(20),
   LastName varchar(20)
   );
Query OK, 0 rows affected (0.46 sec)

Following is the query to create an index −

mysql> create index id_FirstName on DemoTable(Id,FirstName);
Query OK, 0 rows affected (0.52 sec)
Records : 0 Duplicates : 0 Warnings : 0

Following is the query to view the indexes I have set up in MySQL −

mysql> show index from DemoTable;

This will produce the following output displaying the index as well −

+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| DemoTable    | 0          | PRIMARY      | 1            | Id          | A         | 0           | NULL     | NULL   |      | BTREE      |         |               | YES     |
| DemoTable    | 1          | id_FirstName | 1            | Id          | A         | 0           | NULL     | NULL   |      | BTREE      |         |               | YES     |
| DemoTable    | 1          | id_FirstName | 2            | FirstName   | A         | 0           | NULL     | NULL   | YES  | BTREE      |         |               | YES     |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.26 sec)

George John

Published on 13-Jun-2019 08:23:30

• Related Questions & Answers
• How can I view cascades in MySQL?
• How can I dynamically set the position of view in android?
• How can I get up early in the morning?
• I have a startup but sometimes I lack motivation and feel like giving up. How can I keep myself motivated throughout?
• What are the things I have to do every morning when I wake up?
• How do I create a view in MySQL?
• What if I forgot to set Auto Increment? Can I set it later in MySQL?
• Show/view indexes in a MySQL Database
• How do I set the timezone of MySQL?
• How do I set up C/C++ on Eclipse in Windows?
• If I truncate a table, should I also add indexes?
• How can I start MySQL Server?
• How can I shutdown MySQL Server?
• How do I view the auto_increment value for a table in MySQL?
• How can I clone/duplicate the table along with its data, trigger and indexes?