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

Inefficient query in meta enrol plugin

    Details

    • Database:
      MySQL
    • Testing Instructions:
      Hide

      1/ set up some meta course links
      2/ execute meta sync from CLI with verbose flag, verify results
      3/ modify some users in the linked courses
      4/ execute meta sync from CLI with verbose flag, verify results

      Show
      1/ set up some meta course links 2/ execute meta sync from CLI with verbose flag, verify results 3/ modify some users in the linked courses 4/ execute meta sync from CLI with verbose flag, verify results
    • Affected Branches:
      MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE
    • Pull Master Branch:
      MDL-34156-master

      Description

      We have been noticing an extremely long-running SQL query in our MySQL query logs from the enrol_meta_sync function in enrol/meta/locallib.php. (Notice the rows examined is just under 2 billion! Our user_enrolments table has about 187,000-odd rows)

      # Query_time: 1269.173287 Lock_time: 0.000215 Rows_sent: 0 Rows_examined: 1899410877
      SET timestamp=1341238953;
      SELECT ue.*
      FROM mdl_user_enrolments ue
      JOIN mdl_enrol e ON (e.id = ue.enrolid AND e.enrol = 'meta' )
      LEFT JOIN (SELECT xpue.userid, xpe.courseid
      FROM mdl_user_enrolments xpue
      JOIN mdl_enrol xpe ON (xpe.id = xpue.enrolid AND xpe.enrol <> 'meta' AND xpe.enrol IN ('manual','guest','self','cohort','database','meta','category','strathds'))
      ) pue ON (pue.courseid = e.customint1 AND pue.userid = ue.userid)
      WHERE pue.userid IS NULL;

      As far as I can see the subselect is unnecessary and the query is equivalent to this one, which runs in a couple of seconds:

      SELECT ue.*
      FROM mdl_user_enrolments ue
      JOIN mdl_enrol e ON (e.id = ue.enrolid)
      LEFT JOIN (mdl_user_enrolments xpue
      JOIN mdl_enrol xpe ON (xpe.id = xpue.enrolid AND xpe.enrol <> 'meta'
      AND xpe.enrol IN ('manual','guest','self','cohort','database','meta','category','strathds'))
      ) ON (xpe.courseid = e.customint1 AND xpue.userid = ue.userid)
      WHERE e.enrol = 'meta'
      AND xpue.userid IS NULL;

      This is on MySQL 5.5.22

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

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

                  Dates

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