safe_create_drop_index.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS drop_index_if_exists $$
CREATE PROCEDURE drop_index_if_exists(in theTable varchar(128), in theIndexName varchar(128) )
  BEGIN
    IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name =
                                                                                                               theTable AND index_name = theIndexName) > 0) THEN
      SET @s = CONCAT('DROP INDEX `' , theIndexName , '` ON `' , theTable, '`');
      PREPARE stmt FROM @s;
      EXECUTE stmt;
    END IF;
  END $$

DROP PROCEDURE IF EXISTS create_index_if_not_exists $$
CREATE PROCEDURE create_index_if_not_exists(in theTable varchar(128), in theIndexName varchar(128), in theColName varchar(128))
  BEGIN
    IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name =
                                                                                                               theTable AND index_name = theIndexName) = 0) THEN
      SET @s = CONCAT('CREATE INDEX `' , theIndexName , '` ON `' , theTable, '`(`', theColName, '`)');
      PREPARE stmt FROM @s;
      EXECUTE stmt;
    END IF;
  END $$

DELIMITER ;

-- CALL drop_index_if_exists('table_name','index_name');
-- CALL create_index_if_not_exists('table_name','index_name','column_name');