MySQL: Bulk Remove Redundant Index

By | October 31, 2013
  1. Find the [TARGET_DATABSE] and replace it with the target database
  2. Execute the query
  3. Copy the result and execute each of the SQL statement to remove the index
SELECT
CONCAT(GROUP_CONCAT('ALTER TABLE `',tab_left.TABLE_NAME,'` DROP INDEX ',
(
CASE 
WHEN tab_left.COLUMNS = tab_right.COLUMNS AND (tab_left.IS_UNIQUE = tab_right.IS_UNIQUE) 
THEN GREATEST(tab_left.INDEX_NAME, tab_right.INDEX_NAME) 
ELSE tab_left.INDEX_NAME 
END 
) SEPARATOR ';n')) AS q 
FROM 
(
SELECT 
TABLE_NAME, 
CONCAT('`', INDEX_NAME, '`') AS INDEX_NAME, 
INDEX_TYPE, IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE, 
GROUP_CONCAT(
CONCAT(
'`', COLUMN_NAME, '`') ORDER BY 
IF( INDEX_TYPE = 'BTREE', SEQ_IN_INDEX, 0), 
COLUMN_NAME) COLUMNS FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = '[TARGET_DATABASE]' AND TABLE_NAME = TABLE_NAME GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE) AS tab_left 
INNER JOIN (SELECT TABLE_NAME, CONCAT('`', INDEX_NAME, '`') AS INDEX_NAME, INDEX_TYPE, IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE, 
GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY IF(INDEX_TYPE = 'BTREE', SEQ_IN_INDEX, 0), COLUMN_NAME) COLUMNS 
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = '[TARGET_DATABASE]' AND TABLE_NAME = TABLE_NAME 
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE) AS tab_right 
ON tab_left.TABLE_NAME = tab_right.TABLE_NAME AND tab_left.INDEX_NAME != tab_right.INDEX_NAME 
AND tab_left.INDEX_TYPE = tab_right.INDEX_TYPE AND CASE WHEN tab_left.COLUMNS = tab_right.COLUMNS 
AND (tab_left.IS_UNIQUE = 'NO' OR tab_left.IS_UNIQUE = tab_right.IS_UNIQUE) 
THEN TRUE WHEN tab_left.INDEX_TYPE = 'BTREE' AND INSTR(tab_right.COLUMNS, tab_left.COLUMNS) = 1 AND tab_left.IS_UNIQUE = 'NO' 
THEN TRUE ELSE FALSE END GROUP BY tab_left.TABLE_NAME; 

Leave a Reply

Your email address will not be published. Required fields are marked *

1 × 5 =