-
Improvement
-
Resolution: Fixed
-
Minor
-
2.2.2, 2.3.1, 2.4
-
MOODLE_22_STABLE, MOODLE_23_STABLE, MOODLE_24_STABLE
-
MOODLE_23_STABLE
-
w34_
MDL-34864_m24_enrolcat -
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.
- has a clone
-
-
- Closed
-