IDM 126.96.36.199 (including previous 6.5 minor versions) MySQL setup script (openidm.sql) is missing 2 indexes. This is fixed in the 7.0 scripts.
The 6.5.0.x openidm.sql script has:
The 7.0 openidm.sql shows the relationship table setup as follows:
When doing a relationship retrieval, a query similar to the following is executed on the DB:
However as the where clause contains either firstresourceid and secondresourceid, or secondresourceid and firstresourceid - no indexes are matched.
Doing an explain on this with the 188.8.131.52 setup script (missing indexes) shows:
(with a relationship table containing 40k entries)
When applying the missing indexes, the explain shows:
This shows the index being used for the query, and significant performance improvements- even on the limited test system.