Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-15748

Large number of role_capabilities (overrides) affect accesslib performance

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.9.2
    • Fix Version/s: 1.9.5
    • Component/s: Roles / Access
    • Labels:
      None

      Description

      Site with 7 standard roles, normal assignments and 45000 role_capabilities. Running MySQL:

      Running this query from get_user_access_sitewide():

      SELECT sctx.path,
      ra.roleid,
      ctx.path AS parentpath,
      rco.capability,
      rco.permission
      FROM mdl_role_assignments ra
      JOIN mdl_context ctx ON ra.contextid=ctx.id
      JOIN mdl_context sctx ON (sctx.path LIKE CONCAT(ctx.path,'/%') )
      JOIN mdl_role_capabilities rco ON (rco.roleid=ra.roleid AND rco.contextid=sctx.id)
      WHERE ra.userid = 18797
      AND sctx.contextlevel <= 50
      ORDER BY sctx.depth, sctx.path, ra.roleid;

      is 8.2 secs in my blazing fast dev server

      Changing it to:

      SELECT inn.path,
      inn.roleid,
      inn.parentpath,
      rco.capability,
      rco.permission
      FROM mdl_role_capabilities rco
      JOIN (SELECT sctx.id,
      sctx.path,
      sctx.depth,
      ra.roleid,
      ctx.path AS parentpath
      FROM mdl_role_assignments ra
      JOIN mdl_context ctx ON ra.contextid=ctx.id
      JOIN mdl_context sctx ON (sctx.path LIKE CONCAT(ctx.path,'/%') )
      WHERE ra.userid = 18797
      AND sctx.contextlevel <= 50
      ) inn
      ON (rco.roleid=inn.roleid AND rco.contextid=inn.id)
      ORDER BY inn.depth, inn.path, inn.roleid;

      only spends 0.9 secs

      The cause to make that explicit subquery is that it seems that MySQL "delays" queries from tables being searched with LIKE/CONCAT to the end of the execution plan (to save some process). While that's good in general terms... that strategy kills completely our query. We need to get the target contexts first and then, their role_capabilities. And right now MySQL is doing exactly the opposite (with an high cost for sites with thousands of role_capabilities).

      With the subquery... our preferred order is forced. Hence the better times.

      Ciao

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                4 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  13/May/09