MySQL Non-unique Index

http://ift.tt/1AHKBqE

Somebody wanted to know how to find any non-unique indexes in 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. 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; Hope this helps those trying to find non-unique indexes for a table in MySQL.

from Planet MySQL http://ift.tt/1du18ol