MYSQL Drop Index If Exists

mysql_drop_index_if_exists.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 $$

DELIMITER ;

-- CALL drop_index_if_exists('#{index_name}','#{model_id}');

The args in the example at the end are wrong. The first arg should be table_name and the second index_name.

This was an interesting and very useful solution for the drop and add index thing if it does or does not exists. I have used your example to create a stored procedure to add an index safely so, thank you. Maybe one day MySQL will support an

alter table1 add index if not exists idx1 (col1)

where is your sample?

Line 16: https://gist.github.com/drawcode/4441638#file-mysql_drop_index_if_exists-sql-L16