-
Improvement
-
Resolution: Unresolved
-
Major
-
None
-
2.9.4, 3.0.2, 3.2.4, 3.10.4
-
MOODLE_29_STABLE, MOODLE_30_STABLE, MOODLE_310_STABLE, MOODLE_32_STABLE
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
- duplicates
-
MDL-43176 enrol/meta slow database query
-
- Closed
-