OOTB query with insufficient indexing

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: Master
• Fix Version/s: 7.4.3.8 CE GA8, 7.4.13 DXP U4, Master
• Component/s: User Management
• Labels:
  • ee-ts
  • liferay-update-dxp-4-7413
• Git Pull Request: https://github.com/liferay-core-infra/liferay-portal/pull/166

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.}