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

com.liferay.portal.service.persistence.UserFinder.joinByNoOrganizations inefficient

    Details

    • Branch Version/s:
      6.1.x
    • Backported to Branch:
      Committed

      Description

      The following code is very inefficient:

      <sql id="com.liferay.portal.service.persistence.UserFinder.joinByNoOrganizations">
      <![CDATA[
      WHERE
      (User_.userId NOT IN (
      SELECT
      userId
      FROM
      Users_Orgs
      ))
      ]]>
      </sql>

      Changing it to:

      <sql id="com.liferay.portal.service.persistence.UserFinder.joinByNoOrganizations">
      <![CDATA[
      WHERE
      (NOT EXISTS (
      SELECT
      userId
      FROM
      Users_Orgs
      WHERE
      User_.userId=Users_orgs.userId
      ))
      ]]>
      </sql>

      is much more performant

        Issue Links

          Activity

          Hide
          brett.swaim Brett Swaim added a comment -

          This is used when you hit control panel, users and organizations. It shows all the users not in an organization. With thousands of orgs, and hundreds of thousands of users, the original query is very slow.

          Show
          brett.swaim Brett Swaim added a comment - This is used when you hit control panel, users and organizations. It shows all the users not in an organization. With thousands of orgs, and hundreds of thousands of users, the original query is very slow.
          Hide
          shuyang.zhou Shuyang Zhou added a comment -

          Using LEFT JOIN with NULL filtering to get rid of sub-query.
          Query plan comparison:

          1) NOT IN solution

          explain
          select User_.userId
          from User_
          where User_.userId not in (
          select userId
          from Users_Orgs
          );

          -------------------------------------------------------------------------------------------------------------------+

          id select_type table type possible_keys key key_len ref rows Extra

          -------------------------------------------------------------------------------------------------------------------+

          1 PRIMARY User_ index NULL IX_5ADBE171 9 NULL 3 Using where; Using index
          2 DEPENDENT SUBQUERY Users_Orgs index_subquery PRIMARY,IX_FB646CA6 PRIMARY 8 func 1 Using index

          -------------------------------------------------------------------------------------------------------------------+

          2) NOT EXISTS solution

          explain
          select User_.userId
          from User_
          where not exists (
          select userId
          from Users_Orgs
          where User_.userId = Users_Orgs.userId
          );

          --------------------------------------------------------------------------------------------------------------------------+

          id select_type table type possible_keys key key_len ref rows Extra

          --------------------------------------------------------------------------------------------------------------------------+

          1 PRIMARY User_ index NULL IX_5ADBE171 9 NULL 3 Using where; Using index
          2 DEPENDENT SUBQUERY Users_Orgs ref PRIMARY,IX_FB646CA6 PRIMARY 8 lportal.User_.userId 1 Using index

          --------------------------------------------------------------------------------------------------------------------------+

          3) LEFT JOIN with NULL filtering solution

          explain
          select User_.userId
          from User_
          left join Users_Orgs
          on User_.userId = Users_Orgs.userId
          where Users_Orgs.organizationId is null;

          -------------------------------------------------------------------------------------------------------------------------------+

          id select_type table type possible_keys key key_len ref rows Extra

          -------------------------------------------------------------------------------------------------------------------------------+

          1 SIMPLE User_ index NULL IX_5ADBE171 9 NULL 3 Using index
          1 SIMPLE Users_Orgs ref PRIMARY,IX_FB646CA6 PRIMARY 8 lportal.User_.userId 1 Using where; Using index; Not exists

          -------------------------------------------------------------------------------------------------------------------------------+

          Show
          shuyang.zhou Shuyang Zhou added a comment - Using LEFT JOIN with NULL filtering to get rid of sub-query. Query plan comparison: 1) NOT IN solution explain select User_.userId from User_ where User_.userId not in ( select userId from Users_Orgs ); --- ------------------ ---------- -------------- ------------------- ----------- ------- ---- ---- -------------------------+ id select_type table type possible_keys key key_len ref rows Extra --- ------------------ ---------- -------------- ------------------- ----------- ------- ---- ---- -------------------------+ 1 PRIMARY User_ index NULL IX_5ADBE171 9 NULL 3 Using where; Using index 2 DEPENDENT SUBQUERY Users_Orgs index_subquery PRIMARY,IX_FB646CA6 PRIMARY 8 func 1 Using index --- ------------------ ---------- -------------- ------------------- ----------- ------- ---- ---- -------------------------+ 2) NOT EXISTS solution explain select User_.userId from User_ where not exists ( select userId from Users_Orgs where User_.userId = Users_Orgs.userId ); --- ------------------ ---------- ----- ------------------- ----------- ------- -------------------- ---- -------------------------+ id select_type table type possible_keys key key_len ref rows Extra --- ------------------ ---------- ----- ------------------- ----------- ------- -------------------- ---- -------------------------+ 1 PRIMARY User_ index NULL IX_5ADBE171 9 NULL 3 Using where; Using index 2 DEPENDENT SUBQUERY Users_Orgs ref PRIMARY,IX_FB646CA6 PRIMARY 8 lportal.User_.userId 1 Using index --- ------------------ ---------- ----- ------------------- ----------- ------- -------------------- ---- -------------------------+ 3) LEFT JOIN with NULL filtering solution explain select User_.userId from User_ left join Users_Orgs on User_.userId = Users_Orgs.userId where Users_Orgs.organizationId is null; --- ----------- ---------- ----- ------------------- ----------- ------- -------------------- ---- -------------------------------------+ id select_type table type possible_keys key key_len ref rows Extra --- ----------- ---------- ----- ------------------- ----------- ------- -------------------- ---- -------------------------------------+ 1 SIMPLE User_ index NULL IX_5ADBE171 9 NULL 3 Using index 1 SIMPLE Users_Orgs ref PRIMARY,IX_FB646CA6 PRIMARY 8 lportal.User_.userId 1 Using where; Using index; Not exists --- ----------- ---------- ----- ------------------- ----------- ------- -------------------- ---- -------------------------------------+
          Show
          brian.chan Brian Chan added a comment - See https://github.com/brianchandotcom/liferay-portal/pull/9108

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Days since last comment:
                4 years, 5 days ago

                Development

                  Subcomponents