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



      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.


          Issue Links



              sylvia.luan Sylvia Luan
              lavillorar Javier Alpañez (Inactive)
              Participants of an Issue:
              Recent user:
              Jorge Diaz
              1 Vote for this issue
              1 Start watching this issue


                Days since last comment:
                3 years, 48 weeks, 1 day ago


                  Version Package
                  6.2.X EE
                  7.0.1 CE GA2