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

Question

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

How about for a specific table?

"just use : sp_helpindex 'table name' " that seams to be SQL Server (MSSQL) @user3772443 not MySQL — Raymond Nijland
– Raymond Nijland, Commented Oct 21, 2019 at 11:16

Mark Byers · Accepted Answer · 2011-03-06 21:11:19Z

989

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.

edited Mar 6, 2011 at 21:11

answered Mar 6, 2011 at 20:59

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ben Over a year ago

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.

yajant b Over a year ago

@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 ?

Stevers Over a year ago

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?

alecxe · Accepted Answer · 2013-05-17 08:48:04Z

75

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

use information_schema; SELECT * FROM statistics;

edited May 17, 2013 at 8:48

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

answered Mar 6, 2011 at 21:05

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

1 Comment

enharmonic Over a year ago

As a one-liner: SELECT * FROM information_schema.statistics;

alecxe · Accepted Answer · 2013-05-17 08:47:50Z

59

SHOW INDEX FROM mytable FROM mydb; SHOW INDEX FROM mydb.mytable;

See documentation.

edited May 17, 2013 at 8:47

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

answered Mar 6, 2011 at 21:02

LiorK

1,6901 gold badge19 silver badges23 bronze badges

Comments

user16304329 · Accepted Answer · 2021-06-24 06:46:08Z

to see indexes you have created use

SHOW INDEX from your_table_name;

to see all indexes on a table ( created by DB and you)

SHOW EXTENDED INDEX from your_table_name;

simhumileco · Accepted Answer · 2017-08-21 07:38:58Z

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;

Janak · Accepted Answer · 2019-03-27 04:13:48Z

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/

Rick · Accepted Answer · 2021-01-26 12:57:11Z

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.

I was confused looking at the output of SHOW INDEX until I did show create table. I found this answer useful in tandem with other answers.

iamsankalp89 · Accepted Answer · 2018-08-28 12:06:14Z

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';

simhumileco · Accepted Answer · 2017-08-21 06:46:31Z

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.

Digital87 · Accepted Answer · 2018-03-28 06:25:00Z

To check all disabled indexes on db

SELECT INDEX_SCHEMA, COLUMN_NAME, COMMENT FROM information_schema.statistics WHERE table_schema = 'mydb' AND COMMENT = 'disabled'

iamsankalp89 · Accepted Answer · 2018-08-28 12:06:06Z

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;

edited Aug 28, 2018 at 12:06

iamsankalp89

4,7592 gold badges17 silver badges36 bronze badges

answered Oct 3, 2017 at 11:43

Ganesh Giri

1,16113 silver badges18 bronze badges

3 Comments

Ganesh Giri Over a year ago

Paul which tool you are using?This code work for in mysql workbeanch.

Paul Basenko Over a year ago

I used it in MySQL shell

Ganesh Giri Over a year ago

Can you try this one using Mysql Workbench. select * from sys.schema_index_statistics;

Dilshan Dilip · Accepted Answer · 2020-12-29 12:55:59Z

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;

John · Accepted Answer · 2022-02-08 10:15:06Z

select table_name, index_name, seq_in_index, column_name, non_unique, index_type, comment from information_schema.statistics where 1=1 and table_schema = 'my_schema' and table_name = 'my_table' order by 1,2,3,4,5,6

user15292302 · Accepted Answer · 2021-02-26 19:00:18Z

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'

Collectives™ on Stack Overflow

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

14 Answers 14

3 Comments

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

14 Answers 14

3 Comments

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

3 Comments

Comments

Comments

Comments

Linked

Related