[MDL-34776] Some enrolment and role related queries are slow - Moodle Tracker

XML

Word

Printable

Testing Instructions:

Hide

1/ try assigning roles for enrolled users bellow the course level ("locally assigned roles" in module settings block)
2/ try "normal manual" user enrolling
3/ try "normal" assigning of roles to not-enrolled users ("Other users" in course settings block)
4/ disable JS and repeat user ernolment via the old two pane widget
5/ disable JS and repeat other user role assignment via the old two pane widget
6/ try to add/remove enrolled users from a group

Show
1/ try assigning roles for enrolled users bellow the course level ("locally assigned roles" in module settings block) 2/ try "normal manual" user enrolling 3/ try "normal" assigning of roles to not-enrolled users ("Other users" in course settings block) 4/ disable JS and repeat user ernolment via the old two pane widget 5/ disable JS and repeat other user role assignment via the old two pane widget 6/ try to add/remove enrolled users from a group
Affected Branches:
MOODLE_23_STABLE, MOODLE_24_STABLE
Fixed Branches:
MOODLE_23_STABLE
Pull from Repository:
git://github.com/skodak/moodle.git
Pull Master Branch:
w34_~~MDL-34776~~_m24_notinsql
Pull Master Diff URL:
https://github.com/skodak/moodle/compare/master...w34_MDL-34776_m24_notinsql

I have a moodle with 600,000 users enrolled and this query is never completing when simply trying to enrol one user to a course using postgres.

SELECT COUNT(1) FROM mdl_user u

WHERE id <> $1 AND u.deleted = 0 AND u.confirmed = 1

AND u.id NOT IN (SELECT ue.userid

FROM mdl_user_enrolments ue

JOIN mdl_enrol e ON (e.id = ue.enrolid AND e.id = $2))

I'm wondering if we can avoid the subselect somehow, something like:

SELECT COUNT(1) FROM mdl_user u

LEFT OUTER join mdl_user_enrolments ue on u.id = ue.userid and ue.enrolid = $2

WHERE ue IS NULL AND u.id <> $1 AND u.deleted = 0 AND u.confirmed = 1;

Participants:: Andrew Davis, Aparup Banerjee, Dan Poltawski, Eloy Lafuente (stronk7), Petr Skoda

Component watchers:: Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona)