In MySQL we can use the SHOW INDEX statement to return information about the indexes on a table.
We specify which table to show indexes from, and we can optionally specify which database. We can also use a WHERE clause to filter by various criteria.
We can run it against any table where we have some privilege for any column in the table.
Syntax
The syntax goes like this:
SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]So there are various options when it comes to the actual keywords. For example we can use SHOW INDEX, SHOW INDEXES, or SHOW KEYS, all of which do the same thing.
It’s the same with the FROM clause. We can use FROM tbl_name or IN tbl_name. Same thing when specifying the database name.
Notice that we must specify a table name, but specifying the database name is optional.
Example
Here’s a quick example to demonstrate:
SHOW INDEX FROM actor;Result:
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | actor | 0 | PRIMARY | 1 | actor_id | A | 200 | NULL | NULL | | BTREE | | | YES | NULL | | actor | 1 | idx_actor_last_name | 1 | last_name | A | 121 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
This query returns all indexes from the actor table.
Extended Information
We can use the EXTENDED keyword to include information about hidden indexes that MySQL uses internally and are not accessible to users:
SHOW EXTENDED INDEX FROM actor;Result:
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | actor | 0 | PRIMARY | 1 | actor_id | A | 200 | NULL | NULL | | BTREE | | | YES | NULL | | actor | 0 | PRIMARY | 2 | DB_TRX_ID | A | NULL | NULL | NULL | | BTREE | | | YES | NULL | | actor | 0 | PRIMARY | 3 | DB_ROLL_PTR | A | NULL | NULL | NULL | | BTREE | | | YES | NULL | | actor | 0 | PRIMARY | 4 | first_name | A | NULL | NULL | NULL | | BTREE | | | YES | NULL | | actor | 0 | PRIMARY | 5 | last_name | A | NULL | NULL | NULL | | BTREE | | | YES | NULL | | actor | 0 | PRIMARY | 6 | last_update | A | NULL | NULL | NULL | | BTREE | | | YES | NULL | | actor | 1 | idx_actor_last_name | 1 | last_name | A | 121 | NULL | NULL | | BTREE | | | YES | NULL | | actor | 1 | idx_actor_last_name | 2 | actor_id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Specify the Database Name
We also have the option of specifying which database to use:
SHOW INDEX FROM Products FROM KrankyKranes;Result:
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Products | 0 | PRIMARY | 1 | ProductId | A | 8 | NULL | NULL | | BTREE | | | YES | NULL | | Products | 1 | FK_Products_Vendors | 1 | VendorId | A | 4 | NULL | NULL | | BTREE | | | YES | NULL | +----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
That got the indexes from the Products table of the KrankyKranes database.
I actually ran that query from the sakila database. In other words, I was able to get the indexes from a table in a different database to the current database.
That code is the equivalent of the following:
SHOW INDEX FROM KrankyKranes.Products;Result:
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Products | 0 | PRIMARY | 1 | ProductId | A | 8 | NULL | NULL | | BTREE | | | YES | NULL | | Products | 1 | FK_Products_Vendors | 1 | VendorId | A | 4 | NULL | NULL | | BTREE | | | YES | NULL | +----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Filter the Results
We can use a WHERE clause to filter the indexes to just those we’re interested in:
SHOW EXTENDED INDEX FROM actor
WHERE Column_name LIKE '%name%';Result:
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | actor | 0 | PRIMARY | 4 | first_name | A | NULL | NULL | NULL | | BTREE | | | YES | NULL | | actor | 0 | PRIMARY | 5 | last_name | A | NULL | NULL | NULL | | BTREE | | | YES | NULL | | actor | 1 | idx_actor_last_name | 1 | last_name | A | 121 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Alternative Keywords
As mentioned above, the syntax allows some flexibility in the actual keywords we used with this statement. For example we can use SHOW INDEX, SHOW INDEXES, or SHOW KEYS to do the same thing. We can also use FROM or IN when specifying the table and/or database.
So, the following are all equivalent:
SHOW INDEX FROM actor;
SHOW INDEXES FROM actor;
SHOW KEYS FROM actor;
SHOW INDEX IN actor;
SHOW INDEXES IN actor;
SHOW KEYS IN actor;They all produce the following result:
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | actor | 0 | PRIMARY | 1 | actor_id | A | 200 | NULL | NULL | | BTREE | | | YES | NULL | | actor | 1 | idx_actor_last_name | 1 | last_name | A | 121 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+