Slow query in DM

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.2.X EE, 7.0.X EE, Master
• Fix Version/s: 7.0.0 DXP FP23, 7.0.X EE, 7.0.4 CE GA5, 7.1.X, Master
• Component/s: Documents & Media
• Labels:
  • liferay-digital-enterprise-70-sp5
  • liferay-fixpack-de-23-7010
• Branch Version/s:
  7.0.x
• Backported to Branch:
  Committed
• Story Points: 1.25
• Fix Priority: 3
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/50073

We have a query similar to this:

SELECT DLFileEntry.fileEntryId as fileEntr1_703_0_, DLFileEntry.uuid_ as uuid2_703_0_, DLFileEntry.groupId as groupId703_0_, DLFileEntry.companyId as companyId703_0_, DLFileEntry.userId as userId703_0_, DLFileEntry.userName as userName703_0_, DLFileEntry.createDate as createDate703_0_, DLFileEntry.modifiedDate as modified8_703_0_, DLFileEntry.classNameId as classNam9_703_0_, DLFileEntry.classPK as classPK703_0_, DLFileEntry.repositoryId as reposit11_703_0_, DLFileEntry.folderId as folderId703_0_, DLFileEntry.treePath as treePath703_0_, DLFileEntry.name as name703_0_, DLFileEntry.extension as extension703_0_, DLFileEntry.mimeType as mimeType703_0_, DLFileEntry.title as title703_0_, DLFileEntry.description as descrip18_703_0_, DLFileEntry.extraSettings as extraSe19_703_0_, DLFileEntry.fileEntryTypeId as fileEnt20_703_0_, DLFileEntry.version as version703_0_, DLFileEntry.size_ as size22_703_0_, DLFileEntry.readCount as readCount703_0_, DLFileEntry.smallImageId as smallIm24_703_0_, DLFileEntry.largeImageId as largeIm25_703_0_, DLFileEntry.custom1ImageId as custom26_703_0_, DLFileEntry.custom2ImageId as custom27_703_0_, DLFileEntry.manualCheckInRequired as manualC28_703_0_FROM DLFileEntryWHERE(DLFileEntry.smallImageId = :1 ) OR(DLFileEntry.largeImageId = :2 ) OR(DLFileEntry.custom1ImageId = :3 ) OR            (DLFileEntry.custom2ImageId = :4 )

 

Since we are using 4 OR clauses this query performs a full scan in the DB and the performance is poor, and we could not use any index