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

enrol/meta slow database query

    XMLWordPrintable

    Details

    • Affected Branches:
      MOODLE_25_STABLE, MOODLE_26_STABLE, MOODLE_27_STABLE, MOODLE_32_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE
    • Fixed Branches:
      MOODLE_34_STABLE
    • Pull from Repository:
    • Pull Master Branch:
    • Testing Instructions:
      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_test3.php
          47 kB
        2. query_test2.php
          36 kB
        3. query_test.php
          36 kB

          Issue Links

            Activity

              People

              Assignee:
              nmagill Neill Magill
              Reporter:
              apaul Anthony Paul
              Peer reviewer:
              Simey Lameze Simey Lameze
              Integrator:
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Tester:
              Andrew Lyons Andrew Lyons
              Participants:
              Component watchers:
              Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              3 Vote for this issue
              Watchers:
              9 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                13/Nov/17