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

Performance improvement for sites with a lot of role overrides

    XMLWordPrintable

    Details

      Description

      On the big sites, with a lot of rows in mdl_role_capabilities table, this query is very slow:

          $sql = "SELECT ctx.path, rc.roleid, rc.capability, rc.permission
                    FROM {role_capabilities} rc
                    JOIN {context} ctx ON rc.contextid = ctx.id
                   WHERE rc.roleid $sql
                ORDER BY ctx.path, rc.roleid, rc.capability";
      

      See https://github.com/moodle/moodle/blob/master/lib/accesslib.php#L309 for the code of function get_role_definitions_uncached.

      The slowness in MySQL and my data set comes from "ORDER BY ctx.path, rc.roleid, rc.capability". With ordering the query takes 10 minutes and without 25 seconds.
      This is big data and run on slow and not-optimized machine, so the timings are a bit irrelevant - the speed up is.

      In case of this query, ordering is not needed, the results go into associative array:

              $rdefs[$rd->roleid][$rd->path][$rd->capability] = (int) $rd->permission;
      

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                6 Vote for this issue
                Watchers:
                12 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  17/May/18