Mysql: How to see indexes for a database or table?

How do I see if my database has any indexes on it?

How about for a specific table?

 Answers

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.

---

I propose this query:

SELECT DISTINCT s.*
FROM INFORMATION_SCHEMA.STATISTICS s
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t 
    ON t.TABLE_SCHEMA = s.TABLE_SCHEMA 
       AND t.TABLE_NAME = s.TABLE_NAME
       AND s.INDEX_NAME = t.CONSTRAINT_NAME 
WHERE 0 = 0
      AND t.CONSTRAINT_NAME IS NULL
      AND s.TABLE_SCHEMA = 'YOUR_SCHEMA_SAMPLE';

You found all Index only index.

Regard.

---

You can check your indexes in MySQL workbench.under the performance reports tabs you can see all used indexes and unused indexes on the system. or you can fire the query.

select * from sys.schema_index_statistics;