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

Improve category enrolment performance in 2.2 (backport of MDL-34864)

    XMLWordPrintable

Details

    Description

      On a large site with a significant number of category enrolled users, the following query will attempt to run multiple times over, eventually causing performance problems on the database.

      SELECT e.*, ue.userid
                    FROM mdl_enrol e
                    JOIN mdl_context ctx ON (ctx.instanceid = e.courseid AND ctx.contextlevel = '50')
                    JOIN mdl_user_enrolments ue ON (ue.enrolid = e.id)
               LEFT JOIN (SELECT DISTINCT cctx.path, ra.userid
                            FROM mdl_course_categories cc
                            JOIN mdl_context cctx ON (cctx.instanceid = cc.id AND cctx.contextlevel = '40')
                            JOIN mdl_role_assignments ra ON (ra.contextid = cctx.id AND ra.roleid IN ('5','9','11'))
                         ) cat ON (ctx.path LIKE CONCAT(cat.path, '/%') AND cat.userid = ue.userid)
                   WHERE e.enrol = 'category' AND cat.userid IS NULL

      Please optimize this query.

      Attachments

        Issue Links

          Activity

            People

              skodak Petr Skoda
              mchurch Mike Churchward
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: