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

enrol_meta_sync() function is very slow due to poor SQL query

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.9.4, 3.0.2, 3.2.4, 3.10.4
    • Fix Version/s: None
    • Component/s: Enrolments
    • Affected Branches:
      MOODLE_29_STABLE, MOODLE_30_STABLE, MOODLE_310_STABLE, MOODLE_32_STABLE

      Description

      In enrol/meta/lib.php there is the function enrol_meta_sync(). This takes a long time to run on large sites. The problem is due to the big SQL query.

      The expanded version looks like this...

      SELECT ue.userid, ue.enrolid, pue.pstatus, pue.ptimestart, pue.ptimeend
                    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,
                            MIN(CASE WHEN (xpue.status + xpe.status = 0) THEN xpue.timestart ELSE 9999999999 END) AS ptimestart,
                            MAX(CASE WHEN (xpue.status + xpe.status = 0) THEN
                                      (CASE WHEN xpue.timeend = 0 THEN 9999999999 ELSE xpue.timeend END)
                                      ELSE 0 END) AS ptimeend
                            FROM mdl_user_enrolments xpue
                            JOIN mdl_enrol xpe ON (xpe.id = xpue.enrolid AND xpe.enrol <> 'meta' AND xpe.enrol IN ('gudatabase','manual','self','guest','meta'))
                        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)
                   OR ((CASE WHEN pue.ptimestart = 9999999999 THEN 0 ELSE pue.ptimestart END) <> ue.timestart)
                   OR ((CASE WHEN pue.ptimeend = 9999999999 THEN 0 ELSE pue.ptimeend END) <> ue.timeend);
      

      On our site, this takes about four mins to run and returns nothing. Explain looks like this...

      +----+-------------+------------+--------+-----------------------------------------------------------------+-------------------------+---------+--------------------+--------+----------------------------------------------+
      | id | select_type | table      | type   | possible_keys                                                   | key                     | key_len | ref                | rows   | Extra                                        |
      +----+-------------+------------+--------+-----------------------------------------------------------------+-------------------------+---------+--------------------+--------+----------------------------------------------+
      |  1 | PRIMARY     | <derived2> | ALL    | NULL                                                            | NULL                    | NULL    | NULL               | 588328 |                                              |
      |  1 | PRIMARY     | ue         | ref    | mdl_userenro_enruse_uix,mdl_userenro_enr_ix,mdl_userenro_use_ix | mdl_userenro_use_ix     | 8       | pue.userid         |      8 | Using where                                  |
      |  1 | PRIMARY     | e          | eq_ref | PRIMARY,mdl_enro_enr_ix                                         | PRIMARY                 | 8       | moodle2.ue.enrolid |      1 | Using where                                  |
      |  2 | DERIVED     | xpe        | ALL    | PRIMARY,mdl_enro_enr_ix                                         | NULL                    | NULL    | NULL               |  13846 | Using where; Using temporary; Using filesort |
      |  2 | DERIVED     | xpue       | ref    | mdl_userenro_enruse_uix,mdl_userenro_enr_ix                     | mdl_userenro_enruse_uix | 8       | moodle2.xpe.id     |     17 |                                              |
      +----+-------------+------------+--------+-----------------------------------------------------------------+-------------------------+---------+--------------------+--------+----------------------------------------------+
      

      It's searching (in the case of the sub-select) nearly 600,000 rows which isn't very good. If I'm reading this correctly, it returns every single enrolment instance that is not a meta enrolment. That is, almost every enrolment (user) instance on the site

      I'm not entirely sure what this is trying to do but I wonder if it can be split up to make it run a bit faster

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              howardsmiller Howard Miller
              Participants:
              Component watchers:
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona)
              Votes:
              4 Vote for this issue
              Watchers:
              9 Start watching this issue

                Dates

                Created:
                Updated: