Bug #83961 Double indexing in many-to-many relationships
Submitted: 25 Nov 2016 10:07 Modified: 28 Nov 2016 6:29
Reporter: Roman Syroeshko Email Updates:
Status: Verified Impact on me:
Category:MySQL Workbench: Modeling Severity:S2 (Serious)
Version:6.3.8 build 1228 CE (64 bits) OS:Windows (8.1 Single Language (64 bit))
Assigned to: CPU Architecture:Any
Tags: Indexing, many-to-many, modeling, N:M, workbench

[25 Nov 2016 10:07] Roman Syroeshko
Suppose, we have two tables: tableA, and tableZ. Each of the tables has its `id` field which is declared as primary key. `id` fields are indexed with PRIMARY indexes.

When we create many-to-many relationship between the two tables, Workbench creates a new table with three indexes: PRIMARY, fk__tableA_has_tableZ__tableA_id_idx, fk__tableA_has_tableZ__tableZ_id1_idx. The second index is redundant and may cause performance issues, because the correspondent column is already included in PRIMARY index.

The described problem is probably connected with http://bugs.mysql.com/bug.php?id=55497.

How to repeat:
1. Open the attached model (double_indexing_in_many-to-many.mwb).
2. Create `n:m` relationship between the `tableA` and the `tableZ`.
3/ Make sure relationship table is created.
AR: `tableA.id` field is included into two indexes.
ER: `tableA.id` field is included into `PRIMARY` index only.

Suggested fix:
Do not create dedicated index for the field which is the first field in PRIMARY index.
[25 Nov 2016 10:08] Roman Syroeshko
The model.

Attachment: double_indexing_in_many-to-many.mwb (application/octet-stream, text), 7.49 KiB.

[25 Nov 2016 13:44] Chiranjeevi Battula
Hello Roman Syroeshko,

Thank you for the bug report.
I could not see any issues on forward engineering with applying many-to-many relationships in WB 6.3.8 version.
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

[25 Nov 2016 13:45] Chiranjeevi Battula

Attachment: 83961.JPG (image/jpeg, text), 89.92 KiB.

[25 Nov 2016 13:50] Roman Syroeshko
Hello Chiranjeevi Battula,

I was talking about indexes in `tablea_has_tablez` table. It is missed on your screenshot.

[25 Nov 2016 13:55] Roman Syroeshko
BTW, I can't change issue status to Open, because it is not available for me.

Attachment: 83961_cannot_reopen.png (image/png, text), 40.30 KiB.

[28 Nov 2016 6:29] Chiranjeevi Battula
Hello Roman Syroeshko,

Thank you for your feedback.
Verified as described.

[28 Nov 2016 6:30] Chiranjeevi Battula

Attachment: 83961_1.JPG (image/jpeg, text), 196.17 KiB.