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
DROP PROCEDURE IF EXISTS `CreateIndex` $$
CREATE PROCEDURE `CreateIndex`
DECLARE IndexIsThere INTEGER;
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 ',
PREPARE st FROM @sqlstmt;
DEALLOCATE PREPARE st;
SELECT CONCAT('Index ',given_index,' already exists on Table ',
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:
Give it a Try !!!
SHOW CREATE TABLE tablename;) and find out if a similar index exists or not.