528

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

How about for a specific table?

Ramratan Gupta
  • 964
  • 2
  • 13
  • 32
Blankman
  • 236,778
  • 296
  • 715
  • 1,125

12 Answers12

855

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.

Mark Byers
  • 719,658
  • 164
  • 1,497
  • 1,412
  • 10
    Note that the `DISTINCT` keyword will mask some indexes - I have a table where an index has the same name but is used on two different columns, so the information schema example here will only show one index. – Ben Aug 28 '14 at 15:51
  • @Mark Byers Is there a way to see the index table itself? How does SQL generate the index file internally? How does it stores a pointer of a record from index table to Main Table ? – yajant b May 22 '19 at 14:57
  • So my table shows up in INFORMATION_SCHEMA.STATISTICS but it only has one entry, the index_name. There's no additional entry to indicate the column name. All other tables have multiple entries that show something like this: PRIMARY c1, c2 where c1, c2 make up the composite primary key. Any idea why? – Stevers Mar 24 '20 at 12:58
62

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

use information_schema;
SELECT * FROM statistics;
alecxe
  • 414,977
  • 106
  • 935
  • 1,083
RolandoMySQLDBA
  • 41,574
  • 15
  • 86
  • 128
50
SHOW INDEX FROM mytable FROM mydb;

SHOW INDEX FROM mydb.mytable;

See documentation.

alecxe
  • 414,977
  • 106
  • 935
  • 1,083
LiorK
  • 1,450
  • 1
  • 18
  • 22
9

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;
simhumileco
  • 21,911
  • 14
  • 106
  • 90
adeviloper
  • 151
  • 2
  • 4
5

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.

simhumileco
  • 21,911
  • 14
  • 106
  • 90
Thierry
  • 79
  • 1
  • 5
5

To get all indexed columns per index in one column in the sequence order.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2;

Ref: http://blog.9minutesnooze.com/mysql-information-schema-indexes/

Janak
  • 406
  • 4
  • 13
2

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;
iamsankalp89
  • 4,242
  • 2
  • 12
  • 35
Ganesh Giri
  • 728
  • 6
  • 13
2

To check all disabled indexes on db

SELECT INDEX_SCHEMA, COLUMN_NAME, COMMENT 
FROM information_schema.statistics
WHERE table_schema = 'mydb'
AND COMMENT = 'disabled'
Digital87
  • 123
  • 1
  • 5
2

This works in my case for getting table name and column name in the corresponding table for indexed fields.

SELECT TABLE_NAME , COLUMN_NAME, COMMENT 
FROM information_schema.statistics
WHERE table_schema = 'database_name';
iamsankalp89
  • 4,242
  • 2
  • 12
  • 35
asim
  • 76
  • 4
2

Why not show create table myTable ?

Someone told me this but I didn't see anyone mention here, anything bad?

It's neat if you just want to take a glance at the indexes along with column infomations.

Rick
  • 4,467
  • 1
  • 22
  • 49
0

To query the index information of a table, you use the SHOW INDEXES statement as follows:

 SHOW INDEXES FROM table_name;

You can specify the database name if you are not connected to any database or you want to get the index information of a table in a different database:

SHOW INDEXES FROM table_name 
IN database_name;

The following query is similar to the one above:

SHOW INDEXES FROM database_name.table_name;

Note that INDEX and KEYS are the synonyms of the INDEXES, IN is the synonym of the FROM, therefore, you can use these synonyms in the SHOW INDEXES column instead. For example:

SHOW INDEX IN table_name 
FROM database_name;

Or

 SHOW KEYS FROM tablename
 IN databasename;
Dilshan Dilip
  • 494
  • 4
  • 8
0

we can directly see the indexes on to the table if we know the index name with below :

select * from all_indexes where index_name= 'your index'

user15292302
  • 1