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?

score 25 · Accepted Answer · answered May 15 '15 at 12:20

25

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;

answered May 15 '15 at 12:20

Rahul Tripathi

168,305
31
280
331

This is the best answer! – Mad Dog Tannen May 15 '15 at 12:21
1

@RahulTripathi Is it needed to DEALLOCATE the stmt? When does this apply? – Mad Dog Tannen May 15 '15 at 12:31
1

@KayNelson:- DEALLOCATE is not needed. It is used basically if we are using things like cursor. You can refer this: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html which says: *A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it a* – Rahul Tripathi May 15 '15 at 12:32
@KayNelson:- Glad that I helped you as well! – Rahul Tripathi May 15 '15 at 12:37
2

This is equivalent to `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. – The Impaler Jun 11 '22 at 19:34

score 5 · Answer 2 · answered May 15 '15 at 12:20

5

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;

answered May 15 '15 at 12:20

Mad Dog Tannen

7,129
5
31
55

Thank you, but can this be done without store procedure. – Manish Kumar May 15 '15 at 12:29
Yes just execute it in `BEGIN ... END` statements. I always prefeer SP as then you can change logic on a production enviroment without shutting down the software. – Mad Dog Tannen May 15 '15 at 12:30
@KayNelson How do i do this with a field? – Smith Apr 01 '17 at 14:10

score 4 · Answer 3 · answered Mar 01 '19 at 13:00

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;

score 0 · Answer 4 · answered Feb 14 '22 at 21:17

0

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.

answered Feb 14 '22 at 21:17

nhcodes

1,206
8
20

Add index to table if it does not exist

4 Answers4