How can I employ "if exists" for creating or dropping an index in MySQL?

Question

I was wondering if there's a way to check if an index exists before creating it or destroying it on MySQL. It appears that there was a feature request for this a few years back, but I can't find any documentation for a solution. This needs to be done in a PHP app using MDB2.

Solution 1

Here is my 4 liner:

set @exist := (select count(*) from information_schema.statistics where table_name = 'table' and index_name = 'index' and table_schema = database());
set @sqlstmt := if( @exist > 0, 'select ''INFO: Index already exists.''', 'create index i_index on tablename ( columnname )');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;

Solution 2

IF EXISTS modifier is not built for DROP INDEX or CREATE INDEX yet. But you can check manually for the existence before creating/dropping an index.

Use this sentence to check whether the index already exists.

SHOW INDEX FROM table_name WHERE KEY_NAME = 'index_name'

• If the query returns zero (0) then the index does not exists, then you can create it.
• If the query returns a positive number, then the index exists, then you can drop it.

Solution 3

Here is a DROP INDEX IF EXISTS procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS drop_index_if_exists $$
CREATE PROCEDURE drop_index_if_exists(in theTable varchar(128), in theIndexName varchar(128) )
BEGIN
 IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name =
theTable AND index_name = theIndexName) > 0) THEN
   SET @s = CONCAT('DROP INDEX ' , theIndexName , ' ON ' , theTable);
   PREPARE stmt FROM @s;
   EXECUTE stmt;
 END IF;
END $$

DELIMITER ;

This code was created based on the procedure from here: Determining if MySQL table index exists before creating

Solution 4

I tweaked answers found here and else where to come up with the following sprocs for dropping & creating indexes. Note that the AddTableIndex sproc can drop the index if need be. They also accept a schema name which was critical for my uses.

DELIMITER //

DROP PROCEDURE IF EXISTS migrate.DropTableIndex //

CREATE PROCEDURE migrate.DropTableIndex
    (
        in schemaName varchar(128) -- If null use name of current schema;
        , in tableName varchar(128) -- If null an exception will be thrown.
        , in indexName varchar(128) -- If null an exception will be thrown.
    )
BEGIN
    SET schemaName = coalesce(schemaName, schema());
    IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = schemaName and table_name = tableName AND index_name = indexName) > 0) THEN
        SET @s = CONCAT('DROP INDEX `' , indexName , '` ON `' , schemaName, '`.`', tableName, '`');
        PREPARE stmt FROM @s;
        EXECUTE stmt;
    END IF;
END //

DROP PROCEDURE IF EXISTS migrate.AddTableIndex//

CREATE PROCEDURE migrate.AddTableIndex
    ( 
        IN schemaName varchar(128) -- If null use name of current schema;
        , IN tableName varchar(128) -- If null an exception will be thrown.
        , IN indexName varchar(128) -- If null an exception will be thrown.
        , IN indexDefinition varchar(1024) -- E.g. '(expireTS_ ASC)'
        , IN ifPresent ENUM('leaveUnchanged', 'dropAndReplace') -- null=leaveUnchanged.
        , OUT outcome tinyint(1) -- 0=unchanged, 1=replaced, 4=added.
    )
    BEGIN

    DECLARE doDrop tinyint(1) DEFAULT NULL;
    DECLARE doAdd tinyint(1) DEFAULT NULL;
    DECLARE tmpSql varchar(4096) DEFAULT '';

    SET schemaName = coalesce(schemaName, schema());
    SET ifPresent = coalesce(ifPresent, 'leaveUnchanged');
    IF EXISTS (SELECT * FROM   INFORMATION_SCHEMA.STATISTICS WHERE  table_schema = schemaName AND table_name = tableName AND index_name = indexName) THEN
        IF (ifPresent = 'leaveUnchanged') THEN
            SET doDrop = 0;
            SET doAdd = 0;
            SET outcome = 0;
            ELSEIF (ifPresent = 'dropAndReplace')
            THEN
            SET doDrop = 1;
            SET doAdd = 1;
            SET outcome = 1;
        END IF;
    ELSE
        SET doDrop = 0;
        SET doAdd = 1;
        SET outcome = 4;
    END IF;

    IF (doDrop = 1) THEN
        SET tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` drop index `', indexName, '` ');
        SET @sql = tmpSql;
        PREPARE tmp_stmt FROM @sql;
        EXECUTE tmp_stmt;
        DEALLOCATE PREPARE tmp_stmt;
    END IF;

    IF (doAdd = 1) THEN
        SET tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` add index `', indexName, '` (', indexDefinition, ')');
        SET @sql = tmpSql;
        PREPARE tmp_stmt FROM @sql;
        EXECUTE tmp_stmt;
        DEALLOCATE PREPARE tmp_stmt;
    END IF;

    END;
//

DELIMITER ;

Solution 5

I have something similar with using SELECT IF() statement in MySQL.

select if (
    exists(
        select distinct index_name from information_schema.statistics 
        where table_schema = 'schema_db_name' 
        and table_name = 'tab_name' and index_name like 'index_1'
    )
    ,'select ''index index_1 exists'' _______;'
    ,'create index index_1 on tab_name(column_name_names)') into @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

The advantage of using if() statement is that, it doesn’t need a stored procedures.

View more solutions

Comments

[still_dreaming_1]

This query does not work for me. If I delete the WHERE clause it works, but then it is not very helpful. A KEY_name column does get displayed when I delete the WHERE clause though.

[Pablo Venturino]

I just checked again to make sure, and worked for me. Maybe it's a version or configuration issue. I'm using version 5.1.45.

[VenerableAgents]

As someone who loads multiple databases with the same schema, I'd add to the WHERE on the first line: "AND TABLE_SCHEMA = DATABASE()"

[Andrew Gee]

@PabloVenturino can this be checked "programatically" within an SQL statement?

[user3518901]

@VenerableAgents, I just edited the answer to contain that. Saved my life here :)

[ilasno]

Nicely done, just tapped this for a migration script! :) You might want to utilize some _ characters in the procedure names, tho ;)

[PodTech.io]

when you select from information_schema, you should also specify the db.... SELECT @indexCount := COUNT(1) from information_schema.statistics WHERE table_schema='DB' ....... Otherwise you could pick out indexes from different DBs that have the same table and index name - caught me out.

[bmlkc]

This answer is great if you don't want to deal with any stored procedures.

[Mark Davich]

This works well on MariaDB. Workbench highlights the 'if' as if it is an error.

[Rob S]

I think i should read :table_schema = 'database' rather than table_schema = database()

[famas23]

Any idea why we cant do if( @exist > 0, 'create index i_index on tablename ( columnname )'); ?