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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If you want to see all indexes across all databases all at once   use information schema  SELECT   FROM statistics

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

To check all disabled indexes on db  SELECT INDEX SCHEMA  COLUMN NAME  COMMENT  FROM information schema statistics WHERE table schema    mydb  AND COMMENT    disabled

User · Answer

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

User · Answer

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

User · Answer

SHOW INDEX FROM mytable FROM mydb   SHOW INDEX FROM mydb mytable    See documentation

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

Examples related to mysql

Examples related to indexing

Examples related to database-schema