JournalArticleFinderImpl.findByC_G_F_C_A_V_T_D_C_S_T_D_R(..) queries are not optimal when JournalArticleLocalization fields are not used

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.2.X, 7.3.X, Master
• Fix Version/s: 7.2.10 DXP FP14, 7.2.10.5 DXP SP5, 7.2.X, 7.3.10 DXP FP1, 7.3.10.1 DXP SP1, 7.3.6 CE GA7, 7.3.X, 7.4.13 DXP GA1, Master
• Component/s: Web Content > Web Content Administration
• Labels:
  • ee-ts
  • liferay-dxp-72-sp5
  • liferay-dxp-73-sp1
  • liferay-fixpack-dxp-1-7310
  • liferay-fixpack-dxp-14-7210
• Branch Version/s:
  7.3.x, 7.2.x
• Backported to Branch:
  Committed
• Fix Priority: 3
• Git Pull Request: https://github.com/liferay-echo/liferay-portal/pull/3680
• Bug Type: Performance

Reproduction steps:

1. Set up a portal
2. Create a new site
3. Generate 100000 basic web contents
4. Create a new structure (add some dummy fields)
5. Create a few web contents based on the structure
6. Execute the following Groovy script, with replaced structureKey, companyId, groupId:

   import com.liferay.dynamic.data.mapping.service.DDMStructureLocalServiceUtil;
   import java.util.List;
   import com.liferay.dynamic.data.mapping.model.DDMStructure;
   import java.util.ArrayList;
   import com.liferay.journal.service.JournalArticleLocalServiceUtil;
   import com.liferay.portal.kernel.workflow.WorkflowConstants;
   
   String structureKey =  "1151700";
   List<Long> folderIds = new ArrayList<>();
   long companyId=20101;
   long groupId=34753
   JournalArticleLocalServiceUtil.search(companyId, groupId, folderIds, 0, null, null, structureKey, null, null, null, WorkflowConstants.STATUS_APPROVED, null, -1, -1, null);
   

Expected behavior: the query is executed relatively quickly, in less than 1 second.
Actual behavior: the query takes a few seconds to finish.

Notes:

• When you debug into JournalArticleFinderImpl.doFindByC_G_F_C_A_V_T_D_C_S_T_D_R(..), you can read the query from the sql variable. You can see that it joins to JournalArticleLocalization table, even though none of its fields are actually used in the where conditions or in the ORDER BY statement.
• LPS-109979 already solved a similar problem, just with other queries. My guess is that the same would be possible for these complex queries as well.