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

You could just create another table with the correct indices, copy everything from the old table and then drop it and rename the new table back to what the old one used to be. A bit hackish and might be a bit heavy for big tables but still fairly straightforward.

Answer 2

Use SHOW INDEX FROM mytable FROM mydb; and check if the index is present - each of the returned rows represents one part of an index; the column that would probably interest you most is Key_name, as it contains the name of the index. Documentation here.

Answer 3

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

Answer 4

You can query infomration_schema database for this and many more useful information

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

Answer 5

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.