Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-15446

Missing indexes on relationship table

    Details

      Description

      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.

       

       

        Attachments

          Activity

            People

            • Assignee:
              victor.ortega Victor Ortega
              Reporter:
              bradley.tarisznyas Brad Tarisznyas
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: