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

enrol/meta slow database query

    XMLWordPrintable

Details

    • MOODLE_25_STABLE, MOODLE_26_STABLE, MOODLE_27_STABLE, MOODLE_32_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE
    • MOODLE_34_STABLE
    • Hide

      Manual tests

      Create two courses

      1. Course 1
      2. Course 2
      3. In course 1 create the following self enrolments:
        • Student 1: with a start and end date
        • Student 3: suspended, start and end date
        • Student 4: no start or end date
        • Student 5: start and end date
      4. In course 1 create the following manual enrolments:
        • Student 1: start date set to be later than the self enrolment, end date after the self enrolment end date
        • Student 2: suspended
        • Student 3: start date before self enrolment, no end date
        • Student 4: start date and end date after self enrolment end date
        • Student 5: start date after self enrolment start, no end date

      Testing steps

      1. Make a note of the user enrolments in course 1, noting the earliest defined start and the most advantageous end dates for the enrolment methods that a user has
      2. Enter course 2 and create a new meta enrolment instance and set Course 1 as the child course
      3. Verify that the eligible users have been enrolled onto Course 2 and that they have the most advantageous start and end dates on the meta enrolment
      4. Go back to course 1 and make the following changes to enrolment:
        1. Student 1: suspend self enrolment
        2. Student 2: activate manual enrolment
        3. Student 3: activate self enrolment
        4. Student 4: change the start date of manual enrolment
        5. Student 5: set an end date on manual enrolment that is later than the self enrolment end date
      5. On the command line run: php enrol/meta/cli/sync.php --verbose
      6. Go to Course 2 and verify that:
        1. Student 1: the start and end dates match the manual enrolment dates from course 1
        2. Student 2: Is no longer suspended and has the start and end dates from course 1
        3. Student 3: The start date matches the manual enrolment and the end date is unlimited.
        4. Student 4: The start date start date not limited and continues to match the manual enrolment end date
        5. Student 5: The start date matches the self enrolment and the end date matches the manual enrolment

      Unit tests

      Run the meta enrollment unit tests against all supported databases (this will verify that the query functions).

      Run the attached unit test query_test3.php

      Show
      Manual tests Create two courses Course 1 Course 2 In course 1 create the following self enrolments: Student 1: with a start and end date Student 3: suspended, start and end date Student 4: no start or end date Student 5: start and end date In course 1 create the following manual enrolments: Student 1: start date set to be later than the self enrolment, end date after the self enrolment end date Student 2: suspended Student 3: start date before self enrolment, no end date Student 4: start date and end date after self enrolment end date Student 5: start date after self enrolment start, no end date Testing steps Make a note of the user enrolments in course 1, noting the earliest defined start and the most advantageous end dates for the enrolment methods that a user has Enter course 2 and create a new meta enrolment instance and set Course 1 as the child course Verify that the eligible users have been enrolled onto Course 2 and that they have the most advantageous start and end dates on the meta enrolment Go back to course 1 and make the following changes to enrolment: Student 1: suspend self enrolment Student 2: activate manual enrolment Student 3: activate self enrolment Student 4: change the start date of manual enrolment Student 5: set an end date on manual enrolment that is later than the self enrolment end date On the command line run: php enrol/meta/cli/sync.php --verbose Go to Course 2 and verify that: Student 1: the start and end dates match the manual enrolment dates from course 1 Student 2: Is no longer suspended and has the start and end dates from course 1 Student 3: The start date matches the manual enrolment and the end date is unlimited. Student 4: The start date start date not limited and continues to match the manual enrolment end date Student 5: The start date matches the self enrolment and the end date matches the manual enrolment Unit tests Run the meta enrollment unit tests against all supported databases (this will verify that the query functions). Run the attached unit test  query_test3.php

    Description

      Hi,

      We encounter slow-queries generated by "enrol/meta/locallib.php". Executed query looks like this:

      SELECT ue.userid, ue.enrolid, pue.pstatus
                    FROM mdl_user_enrolments ue
                    JOIN mdl_enrol e ON (e.id = ue.enrolid AND e.enrol = 'meta' )
                    JOIN (SELECT xpue.userid, xpe.courseid, MIN(xpue.status + xpe.status) AS pstatus
                            FROM mdl_user_enrolments xpue
                            JOIN mdl_enrol xpe ON (xpe.id = xpue.enrolid AND xpe.enrol <> 'meta' AND xpe.enrol IN ('self','manual','guest','flatfile','database','meta','ldap','cohort'))
                        GROUP BY xpue.userid, xpe.courseid
                         ) pue ON (pue.courseid = e.customint1 AND pue.userid = ue.userid)
                   WHERE (pue.pstatus = 0 AND ue.status > 0) OR (pue.pstatus > 0 and ue.status = 0)
      

      It runs in about 15 seconds here.
      I think we could speed it with this query or something like that (which allows the use of indexes):

      SELECT ue.userid, ue.enrolid, pstatus
        FROM mdl_user_enrolments ue
          JOIN mdl_enrol e ON (e.id = ue.enrolid AND e.enrol = 'meta' AND ue.status=0)
          JOIN (SELECT xpue.userid, xpe.courseid, xpue.status+xpe.status pstatus
                FROM mdl_user_enrolments xpue JOIN mdl_enrol xpe ON (xpe.id = xpue.enrolid AND xpe.enrol <> 'meta')
               ) pue ON (pue.courseid = e.customint1 AND pue.userid = ue.userid)
          WHERE
            pstatus > 0
      UNION
      SELECT ue.userid, ue.enrolid, pstatus
        FROM mdl_user_enrolments ue
          JOIN mdl_enrol e ON (e.id = ue.enrolid AND e.enrol = 'meta' AND ue.status>0)
          JOIN (SELECT xpue.userid, xpe.courseid, xpue.status+xpe.status pstatus
                FROM mdl_user_enrolments xpue JOIN mdl_enrol xpe ON (xpe.id = xpue.enrolid AND xpe.enrol <> 'meta')
               ) pue ON (pue.courseid = e.customint1 AND pue.userid = ue.userid)
          WHERE
            pstatus = 0
      

      And then create indexes on mdl_user_enrolments.status and mdl_enrol.status, so it can run under one second.

      Sorry if I'm wrong since I don't catch the meaning of this query...

      Attachments

        1. query_test.php
          36 kB
        2. query_test2.php
          36 kB
        3. query_test3.php
          47 kB

        Issue Links

          Activity

            People

              nmagill Neill Magill
              apaul Anthony Paul
              Simey Lameze Simey Lameze
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Andrew Lyons Andrew Lyons
              David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo, David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo, Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo
              Votes:
              3 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                13/Nov/17