Show Indexes For A Table In MySQL
When describing a table, such as users:
> describe users;
+------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+----------------+
| id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(80) | NO | | NULL | |
| last_name | varchar(80) | NO | | NULL | |
| email | varchar(80) | NO | UNI | NULL | |
+------------+-----------------------+------+-----+---------+----------------+
We can see in the Key column that there's a primary key and a unique key
for this table on id and email, respectively.
These keys are indexes. To get more details about each of the indexes on
this table, we can use the
show indexes
command.
> show indexes in users;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| users | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE |
| users | 0 | unique_email | 1 | email | A | 0 | NULL | NULL | | BTREE |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+