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

Add index to JournalArticle to speed up expiration checks

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              lu.liu Lu Liu
              Reporter:
              roland.pakai Roland Pákai
              Participants of an Issue:
              Recent user:
              Csaba Turcsan
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                2 years, 25 weeks ago

                  Packages

                  Version Package
                  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