I am trying to add an index to a table, but only if one doesn't exist. I don't know what the index name is. In the mysql manual I found the alter ignore command:

[alter_specification [, alter_specification] ...]

| ADD {INDEX|KEY} [index_name]
    [index_type] (index_col_name,...) [index_option] ...

So, I tried to do the following:

ALTER IGNORE TABLE payments ADD INDEX (id_project);

However, this fails, with the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE payments ADD INDEX (id_project)' 

Turns out, this has been removed as of mysql 5.7.4.

What's a good way to do this in newer versions of mysql?

  • this feature has been removed in 5.7..what version are you using? Jul 14, 2016 at 12:09
  • 5.7.12 ... Is there an easy way to do this under 5.7?
    – maniexx
    Jul 14, 2016 at 12:11
  • You can look at the table (with SHOW CREATE TABLE tablename;) and find out if a similar index exists or not. Jul 14, 2016 at 12:38
  • @ypercubeᵀᴹ But I need this to happen automatically, I am not doing this manually.
    – maniexx
    Jul 14, 2016 at 12:44
  • So, what exactly do you need to do? Add an index, unless the index doesn't exist yet? Jul 14, 2016 at 13:00

3 Answers 3



Changelog 2014-03-27 5.6.17 -- Functionality Added or Changed --

The IGNORE clause for ALTER TABLE ( http://dev.mysql.com/doc/refman/5.6/en/alter-table.html ) is now deprecated and will be removed in a future version of MySQL. ALTER IGNORE TABLE causes problems for replication, prevents online ALTER TABLE for unique index creation, and causes problems with foreign keys (rows removed in the parent table).

  • 1
    Anyway, the IGNORE option was directed at duplicate keys in UNIQUE indexes, not what you needed.
    – Rick James
    Aug 2, 2016 at 16:45

This may help:

  1. Rename the existing table and create a new table.
  2. Use insert ignore to copy data from old table to new table.
  • Very clever solution, I wish I could give you 100 upvotes
    – Umair Ayub
    Aug 30, 2020 at 9:48

I wrote a stored procedure to create indexes and check if one already exists by name.

The code for it is in my answer to MySQL: Create index If not exists I wrote Sep 19, 2012

I tested that code in that old post. It works.

The Stored Procedure checks for the index name only, not the column list combination (See the bottom of this post for what to do if you have such a scenario).

Here is that code from that post

use mydb

    given_database VARCHAR(64),
    given_table    VARCHAR(64),
    given_index    VARCHAR(64),
    given_columns  VARCHAR(64)


    SELECT COUNT(1) INTO IndexIsThere
    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;
        SELECT CONCAT('Index ',given_index,' already exists on Table ',
        given_database,'.',given_table) CreateindexErrorMessage;   
    END IF;

END $$



Now, with the command you used

ALTER IGNORE TABLE payments ADD INDEX (id_project);

you could change it to

ALTER TABLE payments ADD INDEX id_project_ndx (id_project);

Of course, it errors out if the index name id_project_ndx already exists.

If you run the following

ALTER TABLE payments ADD INDEX (id_project);

it will create an index called id_project if one does not exist. If an index called id_project already exists, mysqld will create another index (probably id_project_2 or something like it), but as I indicated in the old post under Create the Index Anyway, it will produce a duplicate index (In this context, a duplicate index is an index that has a different name but has the identical list of columns from another index on the same table). If that happens or if you are not sure duplicate indexes exist, you could download pt-duplicate-key-checker and run it so that it recommends what indexes can be removed and still maintain the same searchability.

By using the stored procedure, you are forced to name the index and columns as follows:

use mydb
call CreateIndex(DATABASE(),'payments','id_project_ndx','id_project'); 

Give it a Try !!!

  • So, without this pt-duplicate-key-checker, there's no way to not create a duplicate, when I don't know the name? That seems like a huge missing feature. Thanks for the answer anyway.
    – maniexx
    Jul 14, 2016 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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