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

DB2 is not using indexes for SB finders which are not unique and return a single value

    Details

    • Fix Priority:
      3

      Description

      Steps to reproduce:

      1. Set up a Liferay Environment with DB2
      2. Enter a million of sites
      3. Stop your app server
      4. Set Portal Property hibernate.show_sql=true
      5. Start your app server
      6. Check the logs
      1. Do a EXPLAIN to obtain the plan for:
        SELECT g.*
        FROM Group_ g
        WHERE group.liveGroupId = ?
        

      Expected results:

      • A code like the following appears:
        select groupimpl0_.groupId as groupId322_, groupimpl0_.uuid_ as uuid2_322_, groupimpl0_.companyId as companyId322_, groupimpl0_.creatorUserId as creatorU4_322_, groupimpl0_.classNameId as classNam5_322_, groupimpl0_.classPK as classPK322_, groupimpl0_.parentGroupId as parentGr7_322_, groupimpl0_.liveGroupId as liveGrou8_322_, groupimpl0_.treePath as treePath322_, groupimpl0_.name as name322_, groupimpl0_.description as descrip11_322_, groupimpl0_.type_ as type12_322_, groupimpl0_.typeSettings as typeSet13_322_, groupimpl0_.manualMembership as manualM14_322_, groupimpl0_.membershipRestriction as members15_322_, groupimpl0_.friendlyURL as friendl16_322_, groupimpl0_.site as site322_, groupimpl0_.remoteStagingGroupCount as remoteS18_322_, groupimpl0_.active_ as active19_322_ 
        from Group_ groupimpl0_ 
        where (groupimpl0_.liveGroupId=? )
        OPTIMIZE FOR 1 ROWS
        
      • Doing a EXPLAIN over this SQL query shows that the plan uses index IX_16218A38

      Actual results:

      • A code like the following appears:
        select groupimpl0_.groupId as groupId322_, groupimpl0_.uuid_ as uuid2_322_, groupimpl0_.companyId as companyId322_, groupimpl0_.creatorUserId as creatorU4_322_, groupimpl0_.classNameId as classNam5_322_, groupimpl0_.classPK as classPK322_, groupimpl0_.parentGroupId as parentGr7_322_, groupimpl0_.liveGroupId as liveGrou8_322_, groupimpl0_.treePath as treePath322_, groupimpl0_.name as name322_, groupimpl0_.description as descrip11_322_, groupimpl0_.type_ as type12_322_, groupimpl0_.typeSettings as typeSet13_322_, groupimpl0_.manualMembership as manualM14_322_, groupimpl0_.membershipRestriction as members15_322_, groupimpl0_.friendlyURL as friendl16_322_, groupimpl0_.site as site322_, groupimpl0_.remoteStagingGroupCount as remoteS18_322_, groupimpl0_.active_ as active19_322_ 
        from Group_ groupimpl0_ 
        where (groupimpl0_.liveGroupId=? )
        
      • Doing a EXPLAIN over this SQL query shows that the plan do not use any index

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Days since last comment:
                1 year, 19 weeks ago

                Packages

                Version Package