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

mysql indexing database-schema
Blankman picture Blankman · Mar 6, 2011 · Viewed 490.2k times · Source

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

How about for a specific table?

Answer

Mark Byers picture Mark Byers · Mar 6, 2011

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.

Related questions

How do I add indices to MySQL tables?

I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run SELECT * FROM table WHERE id = '1'; the code runs fine as the ID field is the primary index. However, for a …

Read More
Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

What are the differences between PRIMARY, UNIQUE, INDEX and FULLTEXT when creating MySQL tables? How would I use them?

Read More
How to drop unique in MySQL?

Create Table: CREATE TABLE `fuinfo` ( `fid` int(10) unsigned NOT NULL, `name` varchar(40) NOT NULL, `email` varchar(128) NOT NULL, UNIQUE KEY `email` (`email`), UNIQUE KEY `fid` (`fid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I want to drop the unique key on email,…

Read More