Add index to table if it doesn't exist
I want to add an index to a table using syntax ALTER
, and I also want to check if the index exists on the table, and if it doesn't exist, add the index.
ALTER TABLE tableName ADD INDEX IX_Table_XYZ (column1);
Is there a way to do this?
+3
Manish kumar
source
to share
2 answers
Try it 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;
+4
Rahul Tripathi
source
to share
You can check if an index exists (by index name) with this syntax
SELECT 1
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'yourschema' AND TABLE_NAME='yourtable' AND
INDEX_NAME='yourindex';
Then you can 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;
+1
Kay nelson
source
to share