CODEWITHSUNDEEP
mysqlindexingalter
Manish Kumar
Manish Kumar

Reputation: 595

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?

Upvotes: 14

Views: 38092

Answers (5)

Michael Kantz
Michael Kantz

Reputation: 187

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;

Upvotes: 0

nhcodes
nhcodes

Reputation: 1954

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.

Upvotes: 1

martoncsukas
martoncsukas

Reputation: 2165

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;

Upvotes: 4

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

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;

Upvotes: 5

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

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;

Upvotes: 26

Related Questions