Search code examples
mysqlindexingalter

Add index to table if it does not exist

I want to add an index to a table by using the ALTER syntax, but first check if it already exists on the table, and only add the index if it does not exist. 

 ALTER TABLE tableName ADD INDEX IX_Table_XYZ (column1);

Is there any way to do this?

Solution

  • Try like this:

    set @x := (select count(*) from information_schema.statistics where table_name = 'table' and index_name = 'IX_Table_XYZ' and table_schema = database());
set @sql := if( @x > 0, 'select ''Index exists.''', 'Alter Table TableName ADD Index IX_Table_XYZ (column1);');
PREPARE stmt FROM @sql;
EXECUTE stmt;