Determining if MySQL table index exists before creating

Question

Our system's automated database migration process involves running .sql scripts containing new table definitions and their accompanying indexes.

I require the ability to create these tables and indexes only if they don't already exist. Tables are taken care of by using IF NOT EXISTS but no such syntax exists when creating indexes.

I've tried to write a stored procedure, shown below, but this fails presumably as you can't select from a show statement.

DELIMITER $$
DROP PROCEDURE IF EXISTS csi_add_index $$
CREATE PROCEDURE csi_add_index(in theTable varchar(128), in theIndexName varchar(128), in theIndexColumns varchar(128)  )
BEGIN
 IF(((SELECT COUNT(*) FROM (SHOW KEYS FROM theTable WHERE key_name = theIndexName)) tableInfo = 0) THEN
   SET @s = CONCAT('CREATE INDEX ' , theIndexName , ' ON ' , theTable, '(', theIndexColumns, ')');
   PREPARE stmt FROM @s;
   EXECUTE stmt;
 END IF;
END $$

I've considered dropping and recreating but the process, as it exists, assumes that it'll encounter no errors hence me wanting to check for existence first.

Is there another way to retrieve the indexes of a table to check if an index already exists before creating or can anyone suggest a better approach to managing this?

EDIT: Please note that this is an automated procedure, no human intervention.

Answer 1

After some more banging my head off the wall and intense googling I found the information_schema.statistics table. This contains the index_name for a table.

My stored procedure is now

DELIMITER $$

DROP PROCEDURE IF EXISTS csi_add_index $$
CREATE PROCEDURE csi_add_index(in theTable varchar(128), in theIndexName varchar(128), in theIndexColumns 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, '(', theIndexColumns, ')');
   PREPARE stmt FROM @s;
   EXECUTE stmt;
 END IF;
END $$

and works as expected.

Thanks for the suggestions.

Answer 2

SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE
`TABLE_CATALOG` = 'def' AND `TABLE_SCHEMA` = DATABASE() AND
`TABLE_NAME` = theTable AND `INDEX_NAME` = theIndexName