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

Question

How can I check if my database has any indexes on it?

How about a particular table?

Answer 1

To check the index for a specific table you have to use "SHOW INDEX":

SHOW INDEX FROM yourtable;

To see indexes for all the tables within a particular 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';

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

Kick-start your career in SQL with the perfect SQL Course by Intellipaat now!