Add index to JournalArticle to speed up expiration checks

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.2.X EE, 7.0.X, Master
• Fix Version/s: 6.2.X EE, 7.0.0 DXP FP47, 7.0.0 DXP SP8, 7.0.X, 7.1.0 Beta 1, 7.1.X, Master
• Component/s: WCM
• Labels:
  • ee-ts
  • liferay-digital-enterprise-70-sp8
  • liferay-fixpack-de-47-7010
  • liferay-fixpack-portal-168-6210
• Branch Version/s:
  7.0.x, 6.2.x
• Backported to Branch:
  Committed
• Fix Priority: 3
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/57788
• Bug Type: Performance

JournalArticleFinder.findByExpirationDate() executes the below query every time it checks to expire articles (default 15):

SELECT * FROM JournalArticle WHERE (classNameId = 0) AND (status = 0) AND (expirationDate <= '2018-04-06 13:42:57');

As there is no index on classNameId, status and expirationDate, the database is executing a full table scan to find the entries. With a table size of ~500k, this takes around 10-20 seconds.

After adding the index, the query finishes almost instantly.