CreateIndices.sql

CreateIndices.sql

-- Creates an index if it does not already exist in MySQL.
-- Code by RolandoMySQLDBA, minor modifications by Adam Matan.
-- License: CC BY-SA, http://creativecommons.org/licenses/by-sa/3.0/
-- Source: http://dba.stackexchange.com/questions/24531/mysql-create-index-if-not-exists/24541#24541


DELIMITER $$

DROP PROCEDURE IF EXISTS `pixels`.`CreateIndex` $$
CREATE PROCEDURE `pixels`.`CreateIndex`
(
    given_database VARCHAR(64),
    given_table    VARCHAR(64),
    given_index    VARCHAR(64),
    given_columns  VARCHAR(64)
)
BEGIN

    DECLARE IndexIsThere INTEGER;
    SELECT COUNT(1) INTO IndexIsThere
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE table_schema COLLATE utf8_unicode_ci = given_database
    AND   table_name   COLLATE utf8_unicode_ci = given_table
    AND   index_name   COLLATE utf8_unicode_ci = given_index;

   IF IndexIsThere = 0 THEN
        SET @sqlstmt = CONCAT('CREATE INDEX ',given_index,' ON ',
        given_database,'.',given_table,' (',given_columns,')');
        PREPARE st FROM @sqlstmt;
        EXECUTE st;
        DEALLOCATE PREPARE st;
        SELECT CONCAT('Created index ', given_table,'.', given_index, ' on columns ', given_columns)
                AS 'CreateIndex status';
    ELSE
        SELECT CONCAT('Index ',given_index,' Already Exists on Table ', given_database,'.',given_table)
                AS 'CreateIndex status';
    END IF;

END $$
DELIMITER ;