CODEWITHSUNDEEP
mysqlindexingreferenceschemacomposite-key
Youness
Youness

Reputation: 2080

How to check indexes on a column in MySQL

My problem is to go over the indexes that refer to a column in MySQL. Is there any fast way to list all the indexes that a column (in a table) participates in.

Upvotes: 0

Views: 334

Answers (2)

Rick James
Rick James

Reputation: 142453

This seems a bit shorter:

select  s.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,s.`TABLE_NAME` AS `TABLE_NAME`,
        s.`INDEX_NAME` AS `INDEX_NAME`,max(s.`NON_UNIQUE`) AS `non_unique`,
        max(if(isnull(s.`SUB_PART`),0,1)) AS `subpart_exists`,
        group_concat(s.`COLUMN_NAME`
            order by  s.`SEQ_IN_INDEX` ASC separator ',' 
                    ) AS `index_columns`
    FROM  `information_schema`.`statistics` AS s2
    JOIN  `information_schema`.`statistics` AS s
            USING (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME)
    WHERE  s2.TABLE_SCHEMA = SCHEMA()
      AND  s2.TABLE_NAME = '...'
      AND  s2.COLUMN_NAME = '...'
    group by  s.`TABLE_SCHEMA`,s.`TABLE_NAME`,s.`INDEX_NAME`;

SEQ_IN_INDEX and COMPOSITE are implicit in my index_columns, which reconstructs the column list.

Upvotes: 1

Youness
Youness

Reputation: 2080

I searched over the web and I couldn't find a clear answer for this question. Then, I figured out to post my solution here. I hope someone might find it useful. This script helps you to detect the composite indexes too. Imagine, we have a Employee table and a id column as the primary key. We want to find all the indexes (single and composite) in the database that Employee.id` is referenced.

SELECT 
    stat.TABLE_NAME,
    stat.INDEX_NAME,
    stat.COLUMN_NAME,
    stat.SEQ_IN_INDEX, 
    statAll.`COMPOSITE`
FROM
information_schema.KEY_COLUMN_USAGE keyUsage
 JOIN INFORMATION_SCHEMA.STATISTICS stat 
  ON (keyUsage.TABLE_NAME = stat.TABLE_NAME AND keyUsage.COLUMN_NAME = stat.COLUMN_NAME) 

 JOIN (SELECT 
        TABLE_NAME,
        INDEX_NAME,
        IF (COUNT(*) > 1, true, false) `COMPOSITE`
       FROM
        INFORMATION_SCHEMA.STATISTICS 
       WHERE
        TABLE_SCHEMA = (SCHEMA())
       GROUP BY 
        TABLE_NAME,
        INDEX_NAME) statAll
  ON (statAll.TABLE_NAME = keyUsage.TABLE_NAME AND statAll.INDEX_NAME = stat.INDEX_NAME)


WHERE
 keyUsage.REFERENCED_TABLE_NAME in ('Employee') -- Table Name
 AND keyUsage.REFERENCED_COLUMN_NAME = 'id' -- Column Name
 AND keyUsage.TABLE_SCHEMA = (SCHEMA())

GROUP BY 
    stat.TABLE_NAME,
    stat.INDEX_NAME,
    stat.COLUMN_NAME,
    stat.SEQ_IN_INDEX

ORDER BY keyUsage.REFERENCED_TABLE_NAME, keyUsage.TABLE_NAME;

The result is, a list of indexes in different tables. Each row contains the following data:

  • TABLE_NAME: The table in which the index is defined,
  • INDEX_NAME: The index name in that table,
  • COLUMN_NAME: The foreign key column name in the table,
  • SEQ_IN_INDEX: Sequence number of the column in the index,
  • COMPOSITE: A flag indicating that the participating column is in a composite key. It shows if the index is composite or single.

Upvotes: 0

Related Questions