Details

    • Branch Version/s:
      6.2.x, 6.1.x
    • Backported to Branch:
      Committed
    • Similar Issues:
      Show 5 results 

      Description

      Description

      The inline permission checker performs poorly on some database (eg. MySQL). A query which involves this sort of permission checking looks like this.

      SELECT COUNT(DISTINCT dlFolder.folderId) AS COUNT_VALUE
      FROM DLFolder dlFolder
      INNER JOIN
        (SELECT SCOPE,
                primKey,
                roleId,
                ownerId
         FROM ResourcePermission
         WHERE (ResourcePermission.companyId = 10154)
           AND (ResourcePermission.name = 'com.liferay.portlet.documentlibrary.model.DLFolder')
           AND (MOD(ResourcePermission.actionIds, 2) = 1)) 
      	InlineSQLResourcePermission ON ((InlineSQLResourcePermission.SCOPE = 4
            	AND InlineSQLResourcePermission.primKey = dlFolder.folderId
            	AND (dlFolder.groupId = 21217
                 AND (InlineSQLResourcePermission.roleId = 10162
                      OR InlineSQLResourcePermission.roleId = 10165
                      OR InlineSQLResourcePermission.ownerId = 21748))))
      WHERE dlFolder.groupId = ?
        AND dlFolder.parentFolderId = ?
      

      As you can see selection predicates of resource permissions are bundled with the join predicates, this way a much larger row source has to be joined to the corresponding entity (ie. DLFolder in this case).

      The query above can be rewritten by moving these predicates to the inner query.

      SELECT COUNT(DISTINCT dlFolder.folderId) AS COUNT_VALUE
      FROM DLFolder dlFolder
      INNER JOIN
        (SELECT SCOPE,
                primKey,
                roleId,
                ownerId
         FROM ResourcePermission
         WHERE (ResourcePermission.companyId = 10154)
           AND (ResourcePermission.name = 'com.liferay.portlet.documentlibrary.model.DLFolder')
           AND (MOD(ResourcePermission.actionIds, 2) = 1)
           AND (ResourcePermission.SCOPE = 4)
           AND (ResourcePermission.roleId = 10162
            OR ResourcePermission.roleId = 10165
            OR ResourcePermission.ownerId = 21748))
      	InlineSQLResourcePermission
         ON (InlineSQLResourcePermission.primKey = dlFolder.folderId)
      WHERE dlFolder.groupId = ?
        AND dlFolder.parentFolderId = ?
      

      This way, subquery InlineSQLResourcePermission results less rows which makes the join more efficient.

      Measurements

      • I created ~2K web content articles and granted ~100 roles to view them.
      • On Oracle there was no difference at all, actually the database came up with exactly the same query plan and estimated the same cost (resource requirement) for both of them.
      • On MySQL however, I measured significant difference in execution time: original query: ~3sec; restructured query: ~100ms.

        Issue Links

          Activity

          Hide
          Raymond Auge added a comment -

          Just started reviewing :)

          Sent from GH.

          Show
          Raymond Auge added a comment - Just started reviewing :) Sent from GH .

            People

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

              Dates

              • Created:
                Updated:
                Resolved:
                Days since last comment:
                1 year, 26 weeks, 4 days ago

                Development

                  Structure Helper Panel