-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 6.5.0.4
-
Fix Version/s: 6.5.1.0
-
Component/s: Module - Repository JDBC
-
Labels:
IDM 6.5.0.4 (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:
-- ----------------------------------------------------- -- Table `openidm`.`relationships` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `openidm`.`relationships` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , `objecttypes_id` BIGINT UNSIGNED NOT NULL , `objectid` VARCHAR(255) NOT NULL , `rev` VARCHAR(38) NOT NULL , `fullobject` MEDIUMTEXT NULL , `firstresourcecollection` VARCHAR(255) , `firstresourceid` VARCHAR(56) , `firstpropertyname` VARCHAR(100) , `secondresourcecollection` VARCHAR(255) , `secondresourceid` VARCHAR(56) , `secondpropertyname` VARCHAR(100) , PRIMARY KEY (`id`) , INDEX `fk_relationships_objecttypes` (`objecttypes_id` ASC) , INDEX `idx_relationships_first_object` (`firstresourcecollection` ASC, `firstresourceid` ASC, `firstpropertyname` ASC) , INDEX `idx_relationships_second_object` (`secondresourcecollection` ASC, `secondresourceid` ASC, `secondpropertyname` ASC) , UNIQUE INDEX `idx_relationships_object` (`objecttypes_id` ASC, `objectid` ASC) , CONSTRAINT `fk_relationships_objecttypes` FOREIGN KEY (`objecttypes_id` ) REFERENCES `openidm`.`objecttypes` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
The 7.0 openidm.sql shows the relationship table setup as follows:
-- ----------------------------------------------------- -- Table `openidm`.`relationships` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `openidm`.`relationships` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , `objecttypes_id` BIGINT UNSIGNED NOT NULL , `objectid` VARCHAR(255) NOT NULL , `rev` VARCHAR(38) NOT NULL , `fullobject` MEDIUMTEXT NULL , `firstresourcecollection` VARCHAR(255) , `firstresourceid` VARCHAR(56) , `firstpropertyname` VARCHAR(100) , `secondresourcecollection` VARCHAR(255) , `secondresourceid` VARCHAR(56) , `secondpropertyname` VARCHAR(100) , PRIMARY KEY (`id`) , INDEX `idx_relationships_first_object` (`firstresourcecollection` ASC, `firstresourceid` ASC, `firstpropertyname` ASC) , INDEX `idx_relationships_second_object` (`secondresourcecollection` ASC, `secondresourceid` ASC, `secondpropertyname` ASC) , INDEX `idx_relationships_originFirst` (`firstresourceid` ASC, `firstresourcecollection` ASC, `firstpropertyname` ASC, `secondresourceid` ASC, `secondresourcecollection` ASC), INDEX `idx_relationships_originSecond` (`secondresourceid` ASC, `secondresourcecollection` ASC, `secondpropertyname` ASC, `firstresourceid` ASC, `firstresourcecollection` ASC), UNIQUE INDEX `idx_relationships_object` (`objectid` ASC), CONSTRAINT `fk_relationships_objecttypes` FOREIGN KEY (`objecttypes_id` ) REFERENCES `openidm`.`objecttypes` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
When doing a relationship retrieval, a query similar to the following is executed on the DB:
SELECT obj.fullobject, obj.objectid, obj.rev, obj.firstresourcecollection, obj.firstresourceid, obj.firstpropertyname, obj.secondresourcecollection, obj.secondresourceid, obj.secondpropertyname FROM openidm.relationships obj INNER JOIN openidm.objecttypes objecttypes ON (obj.objecttypes_id = objecttypes.id AND objecttypes.objecttype = 'relationships') WHERE ((obj.firstresourceid = '02734438-c76d-416b-a282-de9d5209d36a' AND obj.secondresourceid = 'd3cc0db9-367a-4d73-aa9d-f79b65929e39') OR (obj.firstresourceid = 'd3cc0db9-367a-4d73-aa9d-f79b65929e39' AND obj.secondresourceid = '02734438-c76d-416b-a282-de9d5209d36a')) ORDER BY obj.objectid ASC LIMIT 2 OFFSET 0;
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 6.5.0.4 setup script (missing indexes) shows:
mysql> explain SELECT obj.fullobject, obj.objectid, obj.rev, obj.firstresourcecollection, obj.firstresourceid, obj.firstpropertyname, obj.secondresourcecollection, obj.secondresourceid, obj.secondpropertyname FROM openidm.relationships obj INNER JOIN openidm.objecttypes objecttypes ON (obj.objecttypes_id = objecttypes.id AND objecttypes.objecttype = 'relationships') WHERE ((obj.firstresourceid = '02734438-c76d-416b-a282-de9d5209d36a' AND obj.secondresourceid = 'd3cc0db9-367a-4d73-aa9d-f79b65929e39') OR (obj.firstresourceid = 'd3cc0db9-367a-4d73-aa9d-f79b65929e39' AND obj.secondresourceid = '02734438-c76d-416b-a282-de9d5209d36a')) ORDER BY obj.objectid ASC LIMIT 2 OFFSET 0\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: objecttypes partitions: NULL type: const possible_keys: PRIMARY,idx_objecttypes_objecttype key: idx_objecttypes_objecttype key_len: 767 ref: const rows: 1 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: obj partitions: NULL type: ref possible_keys: idx_relationships_object,fk_relationships_objecttypes key: idx_relationships_object key_len: 8 ref: const rows: 17486 filtered: 1.99 Extra: Using index condition; Using where 2 rows in set, 1 warning (0.00 sec)
(with a relationship table containing 40k entries)
When applying the missing indexes, the explain shows:
mysql> explain SELECT obj.fullobject, obj.objectid, obj.rev, obj.firstresourcecollection, obj.firstresourceid, obj.firstpropertyname, obj.secondresourcecollection, obj.secondresourceid, obj.secondpropertyname FROM openidm.relationships obj INNER JOIN openidm.objecttypes objecttypes ON (obj.objecttypes_id = objecttypes.id AND objecttypes.objecttype = 'relationships') WHERE ((obj.firstresourceid = '02734438-c76d-416b-a282-de9d5209d36a' AND obj.secondresourceid = 'd3cc0db9-367a-4d73-aa9d-f79b65929e39') OR (obj.firstresourceid = 'd3cc0db9-367a-4d73-aa9d-f79b65929e39' AND obj.secondresourceid = '02734438-c76d-416b-a282-de9d5209d36a')) ORDER BY obj.objectid ASC LIMIT 2 OFFSET 0\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: objecttypes partitions: NULL type: const possible_keys: PRIMARY,idx_objecttypes_objecttype key: idx_objecttypes_objecttype key_len: 767 ref: const rows: 1 filtered: 100.00 Extra: Using index; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: obj partitions: NULL type: range possible_keys: idx_relationships_object,fk_relationships_objecttypes,idx_relationships_originFirst,idx_relationships_originSecond key: idx_relationships_originSecond key_len: 171 ref: NULL rows: 19 filtered: 5.38 Extra: Using index condition; Using where 2 rows in set, 1 warning (0.00 sec)
This shows the index being used for the query, and significant performance improvements- even on the limited test system.
