Tutorialspoint

1 Answer
George John

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)

Advertisements

We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy.