Bug #83961 Double indexing in many-to-many relationships

• Submitted: 25 Nov 2016 10:07
• Modified: 28 Nov 2016 6:29
• Reporter: Roman Syroeshko
• Status: Verified
• Category: MySQL Workbench: Modeling
• Severity: S2 (Serious)
• Version: 6.3.8 build 1228 CE (64 bits)
• OS: Windows (8.1 Single Language (64 bit))
• CPU Architecture: Any
• Tags: Indexing, many-to-many, modeling, N:M, workbench

[25 Nov 2016 10:07] Roman Syroeshko

Description:
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'.

Thanks,
Chiranjeevi.

[25 Nov 2016 13:45] Chiranjeevi Battula

Screenshot

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.

Thanks.

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

Thanks,
Chiranjeevi.

[28 Nov 2016 6:30] Chiranjeevi Battula

Screenshot

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