MySQL Show Indexes in Database

Sreeram Sreenivasan

Many times you may need to list all indexes in MySQL or display all indexes in MySQL. Here’s how to show indexes in MySQL using SHOW INDEX command in MySQL.

 

MySQL Show Indexes in Database

Here are the steps to show indexes in MySQL. We will use the SHOW INDEXES statement to list all indexes in database.

Here’s the syntax for SHOW INDEXES

SHOW INDEXES FROM table_name;

OR

SHOW INDEXES FROM db_name.table_name;

OR

SHOW INDEXES FROM table_name in db_name;

OR

SHOW KEYS FROM table_name in db_name;

In the above query, you need to specify the table name and database name for which you want to show indexes.

 

Bonus Read : MySQL Compare Databases

 

MySQL Show Indexes for table

Here’s an example to list all indexes for table (e.g sample) in MySQL. Replace it with your table name.

mysql> show indexes from cities in sample;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cities |          0 | PRIMARY    |            1 | city_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| cities |          1 | country_id |            1 | country_id  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

The output displays many attributes of each index including cardinality, key name, column name, etc.

 

Bonus Read : MySQL Alter Stored Procedure

 

MySQL Show Indexes for All Tables in Database

Here’s the SQL query to list all indexes in a specific MySQL database schema (e.g sample) mentioned in bold

mysql> SELECT
         DISTINCT TABLE_NAME,
         INDEX_NAME
      FROM
          INFORMATION_SCHEMA.STATISTICS
      WHERE
          TABLE_SCHEMA = 'sample';
+---------------+---------------+
| TABLE_NAME    | INDEX_NAME    |
+---------------+---------------+
| categories    | PRIMARY       |
| cities        | PRIMARY       |
| cities        | country_id    |
| countries     | PRIMARY       |
| old_orders    | u_orders      |
| orders        | PRIMARY       |
| orders_list   | PRIMARY       |
| recent_orders | order_id      |
| recent_orders | unique_amount |
| x_orders      | PRIMARY       |
+---------------+---------------+

 

Bonus Read : MySQL List All Procedures

 

MySQL SHOW INDEX Size

Index length is stored in information_schema.tables table. Here’s the SQL query to show index size in MySQL for specific database ‘sample’. Replace it with your database name.

mysql> SELECT table_name,
           concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'Mb' ) AS 'data_length_mb',
           concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'Mb' ) AS 'index_length_mb',
           concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'Mb' )  AS 'total_size_mb'
       FROM information_schema.tables
       WHERE table_schema ='sample'
       ORDER BY data_length desc;	
+----------------+----------------+-----------------+---------------+
| table_name     | data_length_mb | index_length_mb | total_size_mb |
+----------------+----------------+-----------------+---------------+
| meeting        | 0.02Mb         | 0.00Mb          | 0.02Mb        |
| sample_data    | 0.02Mb         | 0.00Mb          | 0.02Mb        |
| ...            | ...            | ...             | ...           |
+----------------+----------------+-----------------+---------------+

 

Bonus Read : MySQL Reset Root Password

 

List all Indexes of All Database Schema

Here’s the SQL query to show indexes of all tables in a all MySQL database schema that you have access to.

SELECT
    DISTINCT TABLE_NAME,
    INDEX_NAME
FROM
    INFORMATION_SCHEMA.STATISTICS;

 

Hopefully, now you can easily list indexes in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Related posts:

MySQL Convert Function
How to connect to MySQL on Amazon EC2 from Windows?
How to Create Dynamic Pivot Tables in MySQL