-
Bug
-
Resolution: Fixed
-
Major
-
3.9.9, 3.10.7, 3.11.3, 4.0, 4.0.7, 4.1.2
-
MOODLE_310_STABLE, MOODLE_311_STABLE, MOODLE_39_STABLE, MOODLE_400_STABLE, MOODLE_401_STABLE
-
MOODLE_400_STABLE, MOODLE_401_STABLE
-
mdl-72533-event-table-performance-MOODLE_401_STABLE
-
mdl-72533-event-table-performance
-
-
3
-
HQ Team International Sprin 10, HQ Team International CI H2-21
The events api was updated (MDL-66253) to use a UNION for selecting events of different sources (user, course, category, group), but with a large db of events (~157k), we have found a massive performance hit with this query.
We regularly find query run-times of in excess of 2 seconds, often over 10, which makes the calendar (upcoming events, and timeline blocks) almost unusable.
We noticed the change when the query was changed to include UNION subqueries instead of WHERE.
i.e. Change:
SELECT e.*
|
FROM mdl_event e
|
INNER JOIN (SELECT ev.modulename,
|
ev.instance,
|
ev.eventtype,
|
MIN(ev.priority) as priority
|
FROM (SELECT modulename, instance, eventtype, priority
|
FROM mdl_event ev
|
WHERE (ev.userid = ? AND ev.courseid = 0 AND ev.groupid = 0 AND ev.categoryid = 0) |
UNION SELECT modulename, instance, eventtype, priority
|
FROM mdl_event ev
|
WHERE (ev.groupid IN (?, ?))
|
UNION SELECT modulename, instance, eventtype, priority
|
FROM mdl_event ev
|
WHERE (ev.groupid = 0 AND ev.courseid IN (?, ?) AND ev.categoryid = 0) |
UNION SELECT modulename, instance, eventtype, priority
|
FROM mdl_event ev
|
WHERE (ev.categoryid != 0 AND ev.eventtype = 'category')) ev |
GROUP BY ev.modulename, ev.instance, ev.eventtype) fe
|
ON e.modulename = fe.modulename
|
AND e.instance = fe.instance
|
AND e.eventtype = fe.eventtype
|
AND (e.priority = fe.priority OR (e.priority IS NULL AND fe.priority IS NULL))
|
LEFT JOIN mdl_modules m
|
ON e.modulename = m.name
|
WHERE (m.visible = 1 OR m.visible IS NULL) AND (timestart >= ?) AND timestart <= ? |
AND ((e.userid = ? AND e.courseid = 0 AND e.groupid = 0 AND e.categoryid = 0) |
OR e.groupid IN (?,?)
|
OR (e.groupid = 0 AND e.courseid IN (?,?)) |
OR (e.groupid = 0 AND e.courseid = 0 AND e.categoryid != 0)) |
AND (e.visible = 1) |
ORDER BY COALESCE(e.timesort, e.timestart) ASC, e.id ASC;
|
to:
SELECT e.*
|
FROM mdl_event e
|
LEFT JOIN mdl_modules m
|
ON e.modulename = m.name
|
WHERE (m.visible = 1 OR m.visible IS NULL) AND (timestart >= ?) AND timestart <= ? |
AND ((e.userid = ? AND e.courseid = 0 AND e.groupid = 0 AND e.categoryid = 0) |
OR e.groupid IN (?,?)
|
OR (e.groupid = 0 AND e.courseid IN (?,?)) |
OR (e.groupid = 0 AND e.courseid = 0 AND e.categoryid != 0)) |
AND (e.visible = 1) |
ORDER BY COALESCE(e.timesort, e.timestart) ASC, e.id ASC;
|
The part I'm unsure about is this query being used by different plugins, so perhaps the union is only required under certain circumstances. So am keen for others to look at this as well please.
