MySQL: Create index If not exists

Question

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?

score 74 · Accepted Answer · edited May 25 '21 at 19:09

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 if 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 !!!

It's been 7 years and I cant believe nobody has commented on your awesome pulling in of a table from an old question of OPs. That right there is stroke of genius and I imagine that OP could have learned a lot more from seeing it in THEIR OWN code. Alas seems they never came back, their loss is my gain. Thanks for sharing this :) — William Patton, Nov 04 '19 at 21:54

Mithun B · Answer 2 · 2021-04-28T14:46:54.553

46

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;

Here the select if has this format if (condition, true_case, false_case). The select 'index index_1 exists' is a dummy case. And the _____ plays the role of alias name. If alias is not done then the column name and row both shows index index_1 exists, which would confuse even more. To be more descriptive you can use 'select ''index index_1 exists'' as _______;'.

edited Apr 28 '21 at 14:46

answered Apr 11 '14 at 09:45

Mithun B

561
4
4

1

just a note, you do not need to use distinct when using an exists clause. the exist construct will exit on the first match. adding the distinct is adding the unneeded overhead of an implicit GROUP BY. – user238855 Oct 06 '21 at 15:36
@user238855 This is unfortunately MySQL. The query optimizer doesn't seem to be that smart about handling EXISTS. – dolmen Jan 10 '23 at 15:14
SELECT IF(...) INTO @a; can be simplified as SET @a = IF(...);. – dolmen Jan 10 '23 at 15:15

the_nuts · Answer 3 · 2022-10-25T08:22:01.220

11

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 ($ex->errorInfo[2] == 1061) {
        // Index already exists
    } else {
        // Another error occurred
    }
}

edited Oct 25 '22 at 08:22

answered Jul 17 '19 at 13:24

the_nuts

362
3
11

2

This seems more straightforward than the accepted answer. – Brad Rhoads Feb 15 '22 at 03:39
2

@BradRhoads it does, but it requires using some scripting or programming language. If creating the index is part of an SQL script being run via a db console, this just wouldn't be an option. – SaschaM78 Apr 13 '22 at 13:21

score 7 · Answer 4 · edited Sep 04 '19 at 06:03

7

SELECT COUNT(*)
FROM information_schema.statistics
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'table_name' 
  AND INDEX_NAME = 'index_name';

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.

Tested on MySQL version 5.5.

MariaDB supports IF NOT EXISTS syntax. You can use CREATE INDEX IF NOT EXISTS there.

edited Sep 04 '19 at 06:03

Paul White

83,961
28
402
634

answered Jun 13 '17 at 16:51

Bennet Joseph

181
1
2

If the index includes multiple columns, this query returns the count of rows equal to the count of the index columns. Each row contains the column information that the index was created with. – Ferdinand Prantl Mar 22 '20 at 16:46
1

Ok, thank you for the update. What it really matters is whether the count is zero or greater than zero. – Bennet Joseph Mar 26 '20 at 15:28
'CREATE INDEX IF NOT EXISTS' also works in PostgreSQL – Serafins Nov 30 '20 at 08:48

MySQL: Create index If not exists

4 Answers4

Create the Index Anyway

Check INFORMATION_SCHEMA first

Linked