Add index to table if it does not exist

Question

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?

Answer 1

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;

Answer 2

You can check if the index (by name of the index) exists by using this syntax

SELECT 1        
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'yourschema' AND TABLE_NAME='yourtable' AND
INDEX_NAME='yourindex';

Then you could run it in a Stored Procedure like

IF (SELECT 1        
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = 'yourschema' AND TABLE_NAME='yourtable' AND
    INDEX_NAME='yourindex') != 1 THEN

Alter Table TableName ADD Index IX_Table_XYZ (column1);

END IF;