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?
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;
IF NOT EXISTS and it's a really bad idea. In the end you may end up with an old/obsolete definition of the index in production.
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;
BEGIN ... END statements. I always prefeer SP as then you can change logic on a production enviroment without shutting down the software.
Based on @KayNelson's answer, for me in MySQL 5.7.16 the correct approach was to use IS NULL instead of !=1 in the IF condition.
So a snippet to conditionally add INDEX to a table is:
IF (SELECT 1
FROM `INFORMATION_SCHEMA`.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'yourschema'
AND `TABLE_NAME` = 'yourtable'
AND `INDEX_NAME` = 'yourindex') IS NULL THEN
ALTER TABLE `yourtable` ADD INDEX `yourindex` (`column1` ASC);
END IF;
Another possibility is to check if SHOW INDEX FROM yourTableName WHERE Key_name = 'yourTableIndexName' returns any rows. If the result set isn't empty, the index exists already.
In addition, and based on some answers above. Cleaned some code. Very easy approach.
Just change @tablename and @columnname:
SET @dbname = DATABASE();
SET @tablename = "tableName";
SET @columnname = "IX_Table_XYZ";
SET @preparedStatement = (SELECT IF(
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
WHERE
(table_name = @tablename)
AND (table_schema = @dbname)
AND (index_name = @columnname)
) > 0,
"SELECT 1",
CONCAT("ALTER TABLE ", @tablename, " ADD INDEX ", @columnname, " (", @columnname, ") USING BTREE;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
