59

Is there a way to create an index in MySQL if it does not exist?

MySQL does not support the obvious format:

CREATE INDEX IF NOT EXISTS index_name ON table(column)
ERROR 1064 (42000): You have an error in your SQL syntax;...

MySQL version (mysql -V) is 5.1.48, but I think that MySQL lacks the CREATE INDEX IF NOT EXIST ability in all of its versions.

What's the right way to create an index only if it does not already exist in MySQL?

36

That functionality does not exist. There are two things to keep in mind:

Create the Index Anyway

You can generate index in such a way that the index is created without checking it the index exists ahead of time. For example, you can run the following:

ALTER TABLE table_name ADD INDEX (column_to_index);
ALTER TABLE table_name ADD INDEX (column_to_index);

This will definitely create two indexes without checking. Each index will be assigned a name (perhaps column_to_index,column_to_index_1). Of course, you are trying to avoid that.

Check INFORMATION_SCHEMA first

Here is the layout of INFORMATION_SCHEMA.STATISTICS:

mysql> show create table statistics\G
*************************** 1. row ***************************
       Table: STATISTICS
Create Table: CREATE TEMPORARY TABLE `STATISTICS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `NON_UNIQUE` bigint(1) NOT NULL DEFAULT '0',
  `INDEX_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `INDEX_NAME` varchar(64) NOT NULL DEFAULT '',
  `SEQ_IN_INDEX` bigint(2) NOT NULL DEFAULT '0',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLLATION` varchar(1) DEFAULT NULL,
  `CARDINALITY` bigint(21) DEFAULT NULL,
  `SUB_PART` bigint(3) DEFAULT NULL,
  `PACKED` varchar(10) DEFAULT NULL,
  `NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `INDEX_TYPE` varchar(16) NOT NULL DEFAULT '',
  `COMMENT` varchar(16) DEFAULT NULL,
  `INDEX_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

You could just query for the existence of the index by name. For example, before you run

CREATE INDEX index_name ON mytable(column);

You need to run

SELECT COUNT(1) IndexIsThere FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema=DATABASE() AND table_name='mytable' AND index_name='index_name';

If IndexIsThere is 0, you can create in the index. Perhaps you can write a stored procedure to create an index on the table of your choice.

DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`CreateIndex` $$
CREATE PROCEDURE `adam_matan`.`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 = given_database
    AND   table_name   = given_table
    AND   index_name   = 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;
    ELSE
        SELECT CONCAT('Index ',given_index,' already exists on Table ',
        given_database,'.',given_table) CreateindexErrorMessage;   
    END IF;

END $$

DELIMITER ;

Here is a sample run (Hey Remember This Table? It's from the question you asked back on June 27, 2012) :

mysql> show create table pixels\G
*************************** 1. row ***************************
       Table: pixels
Create Table: CREATE TABLE `pixels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(30) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `pixel_data` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
Query OK, 0 rows affected (0.20 sec)

mysql> show create table pixels\G
*************************** 1. row ***************************
       Table: pixels
Create Table: CREATE TABLE `pixels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(30) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `pixel_data` blob,
  PRIMARY KEY (`id`),
  KEY `type_timestamp_id_ndx` (`type`,`timestamp`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
+-----------------------------------------------------------------------+
| CreateindexErrorMessage                                               |
+-----------------------------------------------------------------------+
| Index type_timestamp_id_ndx Already Exists on Table adam_matan.pixels |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Give it a Try !!!

  • +1, wish I could +100. Your comprehensive answer is awesome. Thanks! – Adam Matan Sep 19 '12 at 18:34
  • Very nice that this can be done with such a procedure. What I don't understand though is why such a verbose procedure is required in MySQL for such a trivial operation. Is there some DB magic going on in the background that I don't understand? – physicalattraction Apr 17 '15 at 12:29
  • @physicalattraction DBs are complicated...so...probably – jsh Jul 15 '16 at 21:17
  • Hi, You can do a 'ALTER TABLE table_name ADD INDEX indexName(column_to_index);' who will fail if this index name already exist ... or create it if it doesn't exist :-) – tdaget Jun 19 '17 at 15:33
  • 1
    Regarding MariaDB @FrankForte - it is actually possible to use CREATE INDEX IF NOT EXISTS: mariadb.com/kb/en/library/create-index – Chris May 17 at 5:18
36

I have something similar with using SELECT IF() statement in MySQL if you are trying not to have procedures.

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;

Hope this will help too.

  • 3
    Welcome to the DBA StackExchange. This is cool. This should help others that do no want stored procedures. I personally recommend that those who use this should copy and paste your answer. Good job. +1 !!! – RolandoMySQLDBA Apr 11 '14 at 11:29
  • this is the best way to do it. +1 – Jakob Oct 2 '15 at 8:31
  • What does the statement 'select ''index index_1 exists'' _______;' do? – Akshay Jun 21 '17 at 5:19
  • 1
    here the select if has this format if (condition, true_case, false_case). the select 'index index_1 exists' is a dummy case. and _____ plays the role of alias name. If alias is not done then the column name and row both shows index index_1 exists, which confuse even more. to be more descriptive you can use 'select ''index index_1 exists'' as _______;'. – Mithun B Jun 22 '17 at 6:18
  • FYI you can't put this on a Flyway migration github.com/flyway/flyway/issues/1500 – mlg Nov 20 '17 at 2:53
2
SELECT COUNT(*)
FROM information_schema.statistics
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'table_name' 
  AND INDEX_NAME = 'index_name'; 
  • For those who did -ve voting, I guess you did not understand the query. My query would give you the count of indexes present on a table with a particular index_name. Based on that count, you can decide whether to issue a CREATE INDEX command or not. - Thanks – Bennet Joseph Sep 19 '17 at 11:19
  • This would be my preferred answer, but it fails on MariaDB 10.2 with 'an error in your SQL syntax'. If you tested it, please add the MySQL version to your answer. – Frank Forte Oct 25 '17 at 14:38
  • Yes, I tested it in MySql version 5.5. Please see this screen shot. – Bennet Joseph Oct 29 '17 at 5:18
  • It works for me on Server version: 10.1.35-MariaDB MariaDB Server – edilio Aug 17 '18 at 13:53
  • Regarding the MariaDB comments: MariaDB actually supports the IF NOT EXISTS syntax: mariadb.com/kb/en/library/create-index You can thus just use CREATE INDEX IF NOT EXISTS there. – Chris May 17 at 5:13
1

If you name the index, the query will fail if the index already exists (tested in MySQL 8.0):

ALTER TABLE `my_table` ADD INDEX `col_idx` (`col` DESC);

Error Code: 1061. Duplicate key name 'col_idx';

So you can just catch the exception and ignore it, for example in PHP:

try {
    $db->query('ALTER TABLE `my_table` ADD INDEX `col_idx` (`col` DESC) VISIBLE;');
} catch (PDOException $ex) {
    if($exception->errorInfo[2] == 1061) {
        // Index already exists
    } else {
        // Another error occurred
    }
}
  • 1
    You get a +1 providing a client-side approach. You should expand your answer in the catch section in case the index creation fails for some other reason (such as out of diskspace, timeout,etc) or at least mention this in your answer. – RolandoMySQLDBA Jul 17 at 13:29
  • @RolandoMySQLDBA edited – the_nuts Jul 17 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.