Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-68951

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

    Details

      Description

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              linda.sui Linda Sui
              Reporter:
              jorge.diaz Jorge Diaz
              Participants of an Issue:
              Recent user:
              Csaba Turcsan
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                5 years, 22 weeks ago

                  Packages

                  Version Package
                  6.2.X EE
                  7.0.0 DXP FP10
                  7.0.0 DXP SP2
                  7.0.3 CE GA4
                  7.1.X
                  Master