Create Indexes for news Columns in DLFileEntry Table

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.2.X EE, 7.0.1 CE GA2
• Fix Version/s: 6.2.X EE, 7.0.1 CE GA2
• Component/s: Documents & Media
• Labels:
  • liferay-fixpack-portal-115-6210
  • performance
• Branch Version/s:
  6.2.x
• Backported to Branch:
  Committed
• Story Points: 1.75
• Fix Priority: 3
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/40316

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.