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

Inefficient query in meta enrol plugin

    XMLWordPrintable

    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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                maherne Michael Aherne
                Reporter:
                maherne Michael Aherne
                Peer reviewer:
                Petr Skoda
                Integrator:
                Dan Poltawski
                Tester:
                David Monllaó
                Participants:
                Component watchers:
                Jake Dallimore, Jun Pataleta, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Víctor Déniz Falcón
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

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