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!
