MySQL Bugs: #53277: Adding foreign key creates unneeded indexes

Bug #53277	Adding foreign key creates unneeded indexes
Submitted:	29 Apr 2010 7:00	Modified:	27 Dec 2012 23:52
Reporter:	Nicholas Sherlock	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.20	OS:	Any
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
If a foreign key is added to a table, MySQL Workbench unconditionally creates a new index over the columns in the foreign key. If an index already exists which the foreign key can use, it should use that instead of creating a new useless index.

How to repeat:
Create two tables:

CREATE TABLE table1 (id INT, PRIMARY KEY(id));
CREATE TABLE table2 (id INT, PRIMARY KEY(id));

In the table editor, create a foreign key for table1 with its 'id' column referencing the 'id' column of the other table. Result: A useless extra index on 'id' is created.

Suggested fix:
Use existing indexes instead of creating new ones for the foreign key if they already exist for the table.

Thank you for the bug report. Indeed, something like these is generated:

-- -----------------------------------------------------
-- Table `mydb`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL ,
  `c1` VARCHAR(45) NULL ,
  PRIMARY KEY (`idtable1`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`table2`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`table2` (
  `idtable2` INT NOT NULL ,
  `c1` INT NULL ,
  PRIMARY KEY (`idtable2`) ,
  INDEX `fk1` (`idtable2` ASC) ,
  CONSTRAINT `fk1`
    FOREIGN KEY (`idtable2` )
    REFERENCES `mydb`.`table1` (`idtable1` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Changed so that an index isnt created if something suitable already exists at the time of the FK creation.

Bug #55619 has been marked as duplicate of this one

I am still experiencing this issue in 5.2.30 on WinXP.

In my case it's :
CREATE  TABLE IF NOT EXISTS `mydb`.`my_table` (
  `my_id` INT NOT NULL ,
  `my_other_id` INT NOT NULL ,
  PRIMARY KEY (`my_id`, `my_other_id`) ,
  INDEX `fk_my_table_my_parent` (`my_id` ASC) ,
  CONSTRAINT `fk_my_table_my_parent`
    FOREIGN KEY (`my_id` )
    REFERENCES `mydb`.`my_parent` (`my_parent_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

In that case the PRIMARY KEY is over two columns, but since the constraint is on the first column of the PK, it should use the PK, not the extra FK index.  It won't let me remove the extra index.

I'm experiencing exactly the same problem: I have a 2 column intersection table, having a foreign key constraint on each column. The primary key is col1, col2 and I manually created a secondary col2, col1 unique index. But WB generates 2 additional indexes on each individual column, and those can't be removed.

I tested a bit and it seems wb *always* creates those additional indexes, independent of the storage engine. I think this is a mistake - whether or not an index is required to implement a foreign key, as well as any additional specifications about what this index should like like, are ultimately a matter of the engine. 

I think it would be best if wb didnt generate any indexes for foreign keys at all. Once the ddl is pushed, indexes will be created as needed by the engine anyway if it requires them.

If wb really insists on creating indexes in advance, it should respect engine specific rules as to how those indexes should look, and if such an index is already present because it was created manually, wb should not generate an additional index.

Still active in version MySQL Workbench 5.2.34 , Revision 7780. It is a problem when you are creating n:m relationship. You have to drop index from DB but your model is different than the version on the server.

I see this in 5.2.35 also.  If you have a multi-field foreign key it creates an index even if there is an index already with the same fields in the same order as a prefix.
The duplication if indices is especially bad since multi-field indices take up more space (naturally).

This issue still exists in MySQL Workbench 5.2.34 CE Revision 7780.

The problem: MySQL Workbench always creates a new index for every FK constraint, regardless of whether it is needed.

If you create a table, for example, with a UNIQUE INDEX on three columns (A, B, C), you do not need a separate index on A for a FOREIGN KEY constraint. But if you create an FK on A in MySQL Workbench, it will create an index on that column. 

See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html, under: "In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. ... InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order."

Why is this a problem? It means that you cannot use the DDL that MySQL Workbench generates without significant auditing and editing. It means that workbench may generate duplicate indexes. It means that you cannot display the indexes in your ERD diagram without misleading the reader.

I found an obscure workaround. To delete the superfluous index, change its type to "unique", then uncheck "UQ" in the Columns tab.

See Bug #66488.

Bug #66488 is a duplicate

Fixed as of the upcoming MySQL Workbench 5.2.45 release, and here's the changelog entry:

When foreign keys are added, existing indexes are now used instead of
always creating new indexes.

Thank you for the bug report.

This is not fixed. I am using version 5.2.47 and have created a foreign key on a column that is the first column of the primary key and it still creates an extra index on that column for no reason. 

Also the work around described by Luke Stevens in 2011 no longer works, so we are stuck with the extra index.

Sorry just to correct the last part of my above comment, the workaround does still work.