MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Non-unique Indexes

with one comment

Somebody wanted to know how to find any non-unique indexes in information_schema of the MySQL. The query takes a session variable with the table name and returns the non-unique indexes by column names. It uses a correlated subquery to exclude the table constraints. A similar query lets you find unique indexes in MySQL. Both queries are in this post.

You set the session variable like this:

SET @sv_table_name := 'member_lab';

You can query the indexes result with the following query:

SELECT   s.table_name
,        s.index_name
,        s.seq_in_index
,        s.column_name
FROM     information_schema.statistics s
WHERE    s.table_name = @sv_table_name
AND      s.non_unique = TRUE
AND      NOT EXISTS
          (SELECT   null
           FROM     information_schema.table_constraints tc
           WHERE    s.table_name = tc.table_name
           AND      s.index_name = tc.constraint_name)
ORDER BY s.table_name
,        s.seq_in_index;

You can also reverse the logic and exclude implicit unique indexes on auto incrementing columns, like

SELECT   s.table_name
,        s.index_name
,        s.seq_in_index
,        s.column_name
FROM     information_schema.statistics s
WHERE    s.table_name = @sv_table_name
AND      s.non_unique = FALSE
AND NOT  s.index_name = 'primary' 
AND      EXISTS
          (SELECT   null
           FROM     information_schema.table_constraints tc
           WHERE    s.table_name = tc.table_name
           AND      s.index_name = tc.constraint_name)
ORDER BY s.index_name
,        s.seq_in_index;

Hope this helps those trying to find non-unique indexes for a table in MySQL.

Written by maclochlainn

December 24th, 2014 at 1:14 am

Posted in MySQL,sql

Tagged with , ,

«
»