Redundant indexes are created where there are composite primary keys or it involves a table mapping

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: Master
• Fix Version/s: 7.1.10 DXP FP12, 7.2.X, 7.2.1 CE GA2, Master
• Component/s: Environments > Databases
• Labels:
  • ee-ts
  • liferay-fixpack-dxp-12-7110
  • pt-app-security-review
• Fix Priority: 3
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/70522

Service Builder does not recognize when a it is creating a redundant index where the table contains a composite primary key or if it is a mapping table. For instance the following queries will show that the Primary index is being used instead of the index created specifically for the columns:

EXPLAIN SELECT * FROM master.Users_Orgs WHERE organizationId = 37094;

EXPLAIN SELECT * FROM UserGroupRole WHERE userId = 20139 AND groupId = 37062;

This cannot be tested manually.