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)
Published on 13-Jun-2019 08:23:30
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP