Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-56639

Create Indexes for news Columns in DLFileEntry Table

    Details

      Description

      When we migrated from Liferay 6.0 to Liferay 6.1 automatically the update script ( "update-6.0.6-6.1.0.sql" ) generates new Fields in DLFileEntry Table:
      ....
      alter table DLFileEntry add smallImageId LONG;
      alter table DLFileEntry add largeImageId LONG;
      alter table DLFileEntry add custom1ImageId LONG;
      alter table DLFileEntry add custom2ImageId LONG;
      ....

      Looking for a better performance we observed in AWR and ADDM Oracle DB that the following sql query behaves too slowly:

      SELECT dlfileentry.fileentryid AS fileentr1_85_0_,
      dlfileentry.uuid_ AS uuid2_85_0_, dlfileentry.groupid AS groupid85_0_,
      dlfileentry.companyid AS companyid85_0_,
      dlfileentry.userid AS userid85_0_,
      dlfileentry.username AS username85_0_,
      dlfileentry.versionuserid AS versionu7_85_0_,
      dlfileentry.versionusername AS versionu8_85_0_,
      dlfileentry.createdate AS createdate85_0_,
      dlfileentry.modifieddate AS modifie10_85_0_,
      dlfileentry.repositoryid AS reposit11_85_0_,
      dlfileentry.folderid AS folderid85_0_, dlfileentry.NAME AS name85_0_,
      dlfileentry.extension AS extension85_0_,
      dlfileentry.mimetype AS mimetype85_0_, dlfileentry.title AS title85_0_,
      dlfileentry.description AS descrip17_85_0_,
      dlfileentry.extrasettings AS extrase18_85_0_,
      dlfileentry.fileentrytypeid AS fileent19_85_0_,
      dlfileentry.VERSION AS version85_0_, dlfileentry.size_ AS size21_85_0_,
      dlfileentry.readcount AS readcount85_0_,
      dlfileentry.smallimageid AS smallim23_85_0_,
      dlfileentry.largeimageid AS largeim24_85_0_,
      dlfileentry.custom1imageid AS custom25_85_0_,
      dlfileentry.custom2imageid AS custom26_85_0_
      FROM dlfileentry
      WHERE (dlfileentry.smallimageid = :1)
      OR (dlfileentry.largeimageid = :2)
      OR (dlfileentry.custom1imageid = :3)
      OR (dlfileentry.custom2imageid = :4)

      Looking the update scripts of later versions ( 6.2, 7.0 ) no actions about it we saw.

      With the unique creation of the indexes we have noticed through performance indicators it has improved considerably:

      create index LPS_DLENT_SMALLIMGID on DLFileEntry (smallimageid);

      create index LPS_DLENT_LARGEIMGID on DLFileEntry (largeimageid);

      create index LPS_DLENT_CUSTOM1IMGID on DLFileEntry (custom1imageid);

      create index LPS_DLENT_CUSTOM2IMGID on DLFileEntry (custom2imageid);

      We recommend add as improvement.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  2 years, 50 weeks, 4 days ago

                  Packages

                  Version Package
                  6.2.X EE
                  7.0.1 CE GA2