CODEWITHSUNDEEP
mysqlindexing
wamp
wamp

Reputation: 5969

How to conditionally create index with MySQL?

What I want to do is something like follows:

CREATE INDEX i_table_column 
ON table(column) 
IF NOT EXISTS INDEX ON table(column)

Is it possible to write the above statement in MySQL?

Upvotes: 3

Views: 6510

Answers (1)

Mike
Mike

Reputation: 21659

You can alter the example given by Mitch Wheat so that it checks to see if an index exists for a given column name, but does not check the index name itself:

delimiter //
DROP PROCEDURE IF EXISTS create_index_if_not_exists//
CREATE PROCEDURE `create_index_if_not_exists` (IN param_schema CHAR(255), IN param_table CHAR(255), IN param_column CHAR(255))
BEGIN

    SELECT @indexes := COUNT(*)
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE table_schema = param_schema
        AND table_name = param_table
        AND COLUMN_NAME = param_column;

    IF @indexes = 0 THEN
        SET @sql_cmd := CONCAT(
            'ALTER TABLE ',
            param_table,
            ' ADD INDEX ',
            '`', param_column, '` ',
            '(', param_column, ')');
        SELECT @sql_cmd;
        PREPARE stmt FROM @sql_cmd;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

END//
delimiter ;

Example:

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `col1` int(10) unsigned,
  `col2` int(10) unsigned,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

CALL create_index_if_not_exists('test', 'table1', 'col2');

+--------------------------------------------+
| @sql_cmd                                   |
+--------------------------------------------+
| ALTER TABLE table1 ADD INDEX `col1` (col1) |
+--------------------------------------------+

SHOW CREATE TABLE `table1`;

CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `col1` int(10) unsigned default NULL,
  `col2` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The following line displays the command that is to be executed, and may be removed from the stored procedure if necessary:

SELECT @sql_cmd;

Upvotes: 4

Related Questions