How to see indexes for a database or table in MySQL?

If you want to see all indexes across all databases all at once:

use information_schema;
SELECT * FROM statistics;

You could use this query to get the no of indexes as well as the index names of each table in specified database.

SELECT TABLE_NAME,
       COUNT(1) index_count,
       GROUP_CONCAT(DISTINCT(index_name) SEPARATOR ',\n ') indexes
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'mydb'
      AND INDEX_NAME != 'primary'
GROUP BY TABLE_NAME
ORDER BY COUNT(1) DESC;

To see the index for a specific table use SHOW INDEX:

SHOW INDEX FROM yourtable;

To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

Removing the where clause will show you all indexes in all schemas.


SHOW INDEX FROM mytable FROM mydb;

SHOW INDEX FROM mydb.mytable;

See documentation.