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

Some enrolment and role related queries are slow

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.3.1, 2.4
    • Fix Version/s: 2.3.2
    • Component/s: Enrolments
    • Labels:
    • 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:
    • Pull Master Branch:
      w34_MDL-34776_m24_notinsql

      Description

      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;

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  10/Sep/12