Add index to table if it does not exist

mysql indexing alter
Manish Kumar picture Manish Kumar · May 15, 2015 · Viewed 18.2k times · Source

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

Rahul Tripathi picture Rahul Tripathi · May 15, 2015

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;

Related questions

How do I add indices to MySQL tables?

I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run SELECT * FROM table WHERE id = '1'; the code runs fine as the ID field is the primary index. However, for a …

Read More
How to see indexes for a database or table in MySQL?

How do I see if my database has any indexes on it? How about for a specific table?

Read More
Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

What are the differences between PRIMARY, UNIQUE, INDEX and FULLTEXT when creating MySQL tables? How would I use them?

Read More