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
    • Similar Issues:
      Show 5 results 

      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

        Activity

        Hide
        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 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 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 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 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:
              2 years, 5 weeks, 3 days ago

              Development

                Structure Helper Panel