MySQL Bugs: #55619: Existing indexes will be ignored on creating foreign keys

Bug #55619	Existing indexes will be ignored on creating foreign keys
Submitted:	29 Jul 2010 3:29	Modified:	29 Jul 2010 10:34
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.25 SE	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When you create a foreign key with Workbench then Workbench is ignoring existing indexes.

It will double create indexes.

Also validation check won't recognise it.

My tests how InnoDB is behaving on that topic you will find in my explaining_index.txt file.

Workbench fails in all InnoDB examples I tested.

For the first example I made two movies. But of course when you try to create the other examples via Workbench you will get similar failures.

Two indexes for same column will ...
1. confuse the optimizer
1.1. that will occur performance issues
2. indexes cost space

How to repeat:
Read explaining_index.txt
Watch my movies

Suggested fix:
Check if index already exist before creating a new index.

How InnoDB reacts on existing indexes after creating a foreign key

Attachment: explaining_index.txt (text/plain), 1.89 KiB.

Wrong Workbench behaviour during modeling

Attachment: fk_indexing1.ogv (video/ogg, text), 226.80 KiB.

Wrong Workbench behaviour during forward engineering

Attachment: fk_indexing2.ogv (video/ogg, text), 450.67 KiB.

I forgot to mention that of course after forward engineer the table will have two indexes for same column on MySQL server.

fix confirmed in repository

sorry, wrong comment, its still in progress, but this is actually a duplicate of Bug# 53277