How can I employ "if exists" for creating or dropping an index in MySQL?
PhpMysqlIndexingPhp Problem Overview
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.
Php Solutions
Solution 1 - Php
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 - Php
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 - Php
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: https://stackoverflow.com/questions/3626645/determining-if-mysql-table-index-exists-before-creating
Solution 4 - Php
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 - Php
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.
Solution 6 - Php
I think this will helpful to you drop your existing index.
DELIMITER //
CREATE PROCEDURE dropIndexing
()
BEGIN
IF EXISTS(
SELECT * FROM information_schema.statistics
WHERE TABLE_SCHEMA = DATABASE()
AND `table_name`='mytable'
AND `index_name` = 'myindex'
)
THEN
ALTER TABLE `mytable` DROP INDEX `myindex`;
END IF;
END //
DELIMITER ;
CALL dropIndexing();
DROP PROCEDURE dropIndexing;
Solution 7 - Php
I was having problems with some of the solutions presented here. This is what I came up with:
DELIMITER $$
DROP PROCEDURE IF EXISTS myschema.create_index_if_not_exists $$
CREATE PROCEDURE myschema.create_index_if_not_exists(in p_tableName VARCHAR(128), in p_indexName VARCHAR(128), in p_columnName VARCHAR(128) )
BEGIN
PREPARE stmt FROM 'SELECT @indexCount := COUNT(1) from information_schema.statistics WHERE `table_name` = ? AND `index_name` = ?';
SET @table_name = p_tableName;
SET @index_name = p_indexName;
EXECUTE stmt USING @table_name, @index_name;
DEALLOCATE PREPARE stmt;
-- select @indexCount;
IF( @indexCount = 0 ) THEN
SELECT 'Creating index';
SET @createIndexStmt = CONCAT('CREATE INDEX ', p_indexName, ' ON ', p_tableName, ' ( ', p_columnName ,')');
PREPARE stmt FROM @createIndexStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END $$
DELIMITER ;
Use it as follows:
call myschema.create_index_if_not_exists('MyTable','end_time_index','end_time');
This was tested on MAC OS X 10.8.2 with MySQL 5.5.24 and on Windows 7 with MySQL 5.5.21
Solution 8 - Php
MySQL Workbench version 6.3 (MySql fork MariaDb)
DROP INDEX IF EXISTS FK_customer__client_school__school_id ON dbname.tablename;
Solution 9 - Php
Here is a workaround for the DROP INDEX IF EXISTS, that is missing in MySQL and MariaDB versions before v10.1.4. You can also use it for every other statement you want, that should be depend on the existence of an INDEX (e.g. for SELECT "info: index exists." like in the example below).
-- DROP INDEX IF EXISTS
SELECT
COUNT(*)
INTO
@INDEX_my_index_ON_TABLE_my_table_EXISTS
FROM
`information_schema`.`statistics`
WHERE
`table_schema` = 'my_database'
AND `index_name` = 'my_index'
AND `table_name` = 'my_table'
;
SET @statement := IF(
@INDEX_my_index_ON_TABLE_my_table_EXISTS > 0,
-- 'SELECT "info: index exists."',
'DROP INDEX `my_index` ON `my_table`',
'SELECT "info: index does not exist."'
);
PREPARE statement FROM @statement;
EXECUTE statement;