Details

    Description

      Background
      Our client has reported the following slow running query:

      select this_.contactId as contactId6_0_, this_.mvccVersion as mvccVers2_6_0_, this_.companyId as companyId6_0_, this_.userId as userId6_0_, this_.userName as userName6_0_, this_.createDate as createDate6_0_, this_.modifiedDate as modified7_6_0_, this_.classNameId as classNam8_6_0_, this_.classPK as classPK6_0_, this_.accountId as accountId6_0_, this_.parentContactId as parentC11_6_0_, this_.emailAddress as emailAd12_6_0_, this_.firstName as firstName6_0_, this_.middleName as middleName6_0_, this_.lastName as lastName6_0_, this_.prefixId as prefixId6_0_, this_.suffixId as suffixId6_0_, this_.male as male6_0_, this_.birthday as birthday6_0_, this_.smsSn as smsSn6_0_, this_.facebookSn as facebookSn6_0_, this_.jabberSn as jabberSn6_0_, this_.skypeSn as skypeSn6_0_, this_.twitterSn as twitterSn6_0_, this_.employeeStatusId as employe25_6_0_, this_.employeeNumber as employe26_6_0_, this_.jobTitle as jobTitle6_0_, this_.jobClass as jobClass6_0_, this_.hoursOfOperation as hoursOf29_6_0_ from Contact_ this_ where this_.contactId>? and this_.companyId=? and this_.classPK=? order by this_.contactId asc limit ?
      

      The query calls for the contactId , companyId and classPK
      But the Contact_ table only has these indexes present OOTB for DXP 7.2 (dxp-7)

        PRIMARY KEY (`contactId`),
        KEY `IX_B8C28C53` (`accountId`),
        KEY `IX_791914FA` (`classNameId`,`classPK`),
        KEY `IX_66D496A3` (`companyId`)
      

      Issue
      When a user logs in, the above query is executed but due to the amount of data in Contact_, the query is very slow.

      Steps to reproduce

      1. Add these properties to portal-ext.properties
        admin.sync.default.associations=false
        users.update.last.login=false
      2. Start a clean 7.2 dxp-7 bundle and login
      3. Create a new user: Bob Smith
        Membership: Liferay DXP
        Regular Role: Administrator
        Password: test
      4. Add DEBUG log for the following classes
        org.hibernate.SQL
        org.hibernate.type
      5. Log out of Test (Admin) then login with Bob Smith
      6. Observe server logs for reported query

      Expected result
      The described query runs and shows in logs.

      Actual result
      The query is not executed.

      Reproduced in

      • DXP 7.2, dxp-7
      • 7.2.x (d264f1bf90505b73cb4f62d770476a3ff8e826d1)

      Not Reproduced in

      • master (e9ffd7d5755fee2b87862c878c1912e528586938)

      * Please see Zendesk Support tab for further comments and attachments.

      Attachments

        Issue Links

          Activity

            People

              team-core-infrastructure Product Team Core Infrastructure
              hong.vo Hong Vo
              Kiyoshi Lee Kiyoshi Lee
              Hong Vo Hong Vo
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                1 year, 2 weeks, 4 days ago

                Packages

                  Version Package
                  7.4.3.8 CE GA8
                  7.4.13 DXP U4
                  Master