mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead?

Question

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 [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
alter_specification:
table_options
...
| 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?

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

Answer 1

FYI...

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).

• Rick James: Anyway, the IGNORE option was directed at duplicate keys in UNIQUE indexes, not what you needed.

Answer 2

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.

• Umair Ayub: Very clever solution, I wish I could give you 100 upvotes

Answer 3

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
DELIMITER $$
DROP PROCEDURE IF EXISTS `CreateIndex` $$
CREATE PROCEDURE `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 ;

REGARDING DUPLICATE INDEXES

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

• maniexx: 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.