UserFinderImpl.countByC_FN_MN_LN_SN_EA_S is slow in case of using database instead index and having a lot of users

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.2.10 EE GA1, 6.2.X EE, 7.0.0 DXP GA1, 7.0.0 DXP SP2, Master
• Fix Version/s: 6.2.X EE, 7.0.0 DXP FP10, 7.0.0 DXP SP2, 7.0.3 CE GA4, 7.1.X, Master
• Component/s: User Management
• Labels:
  • ee-ts
  • liferay-digital-enterprise-70-sp2
  • liferay-fixpack-de-10-7010
  • liferay-fixpack-portal-133-6210
• Branch Version/s:
  7.0.x, 6.2.x
• Backported to Branch:
  Committed
• Story Points: 1.25
• Fix Priority: 3
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/45521

UserFinderImpl.countByC_FN_MN_LN_SN_EA_S is slow in case of using database instead index (users.indexer.enabled=false and users.search.with.index=false) and having a lot of users, we are retrieving all users from database in order to count them:

1. SQL: select userId from User_ .......
2. Java: userIds.size();

   @Override
   public int countByC_FN_MN_LN_SN_EA_S(
   	long companyId, String[] firstNames, String[] middleNames,
   	String[] lastNames, String[] screenNames, String[] emailAddresses,
   	int status, LinkedHashMap<String, Object> params, boolean andOperator) {
   
   	List<Long> userIds = doFindByC_FN_MN_LN_SN_EA_S(
   		companyId, firstNames, middleNames, lastNames, screenNames,
   		emailAddresses, status, params, andOperator, QueryUtil.ALL_POS,
   		QueryUtil.ALL_POS, null);
   
   	return userIds.size();
   }
   

We should avoid this and execute a SQL with count(userId)

Steps to reproduce

1. Configure in portal-ext.properties: users.indexer.enabled=false and users.search.with.index=false
2. Create 1000000 users in Liferay
3. Execute attanched groovy script searchCount.groovy
   • Without changing SQL: UserLocalServiceUtil.searchCount spends 5 or 7 seconds getting all userIds and counting them
   • After changing SQL to select count(userId): Time is reduced to 50%, it spends 3 or 4 seconds