verifyArticleAssets() generates long running SQL query.

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.0.X EE, Master
• Fix Version/s: 7.0.0 DXP FP23, 7.0.X EE, 7.0.4 CE GA5, 7.1.X, Master
• Component/s: Web Content > Web Content Administration
• Labels:
  • liferay-digital-enterprise-70-sp5
  • liferay-fixpack-de-23-7010
• Branch Version/s:
  7.0.x
• Backported to Branch:
  Committed
• Fix Priority: 3
• Git Pull Request: https://github.com/liferay/com-liferay-journal/pull/367

verifyArticleAssets() generates long running SQL query with MySQL 5.6 This query has been defined at : /modules/apps/web-experience/journal/journal-service/src/main/resources/META-INF/custom-sql/default.xml r

SELECT {JournalArticle.*} FROM JournalArticle LEFT JOIN AssetEntry ON (AssetEntry.classPK = JournalArticle.resourcePrimKey) WHERE AssetEntry.classPK IS NULL 

Run time of this query was more than 5 hours:

INFO - Completed com.liferay.journal.verify.JournalServiceVerifyProcess#verifyArticleAssets in 20411095 ms

When the query was changed to

 SELECT {JournalArticle.*} FROM JournalArticle WHERE JournalArticle.resourcePrimKey NOT IN ( SELECT DISTINCT ResourcePermission.primKeyId FROM ResourcePermission WHERE (ResourcePermission.name = 'com.liferay.journal.model.JournalArticle') AND (ResourcePermission.primKeyId = JournalArticle.resourcePrimKey) AND (ResourcePermission.scope = ?)	) 

execution was faster 11 minutes.

INFO - Completed com.liferay.journal.verify.JournalServiceVerifyProcess#verifyArticleAssets in 1265349 ms