Details
-
Improvement
-
Status: Closed
-
Major
-
Resolution: Fixed
-
3.6.4, 3.7.1, 3.8.1, 3.9
-
Any
-
MOODLE_36_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
-
MOODLE_39_STABLE
-
Description
The way the events API queries the database for events can be inefficient resulting many in full table scans on the database.
For example the AJAX calls from the timeline block always result in a query that does a full table scan (calendar_action_events_by_timesort web service) and are used by the upcoming events block.
These issues can be traced to the queries built core_calendar\local\event\strategies\raw_event_retrieval_strategy class.
For example queries like the following are often run on our Moodle instance:
SELECT e.* |
FROM mdl_event e |
INNER JOIN (SELECT ev.modulename, |
ev.instance,
|
ev.eventtype,
|
MIN(ev.priority) AS priority |
FROM mdl_event ev |
WHERE (ev.userid = ? AND ev.courseid = ? AND ev.groupid = ? AND ev.categoryid = ?) OR (ev.groupid != ? AND ev.eventtype = ?) OR (ev.groupid = ? AND ev.courseid IN (?,?) AND ev.categoryid = ?) OR (ev.categoryid != ? AND ev.eventtype = ?) |
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 = ? OR m.visible IS NULL) AND type = ? AND timesort >= ? AND timesort <= ? AND ((e.userid = ? AND e.courseid = ? AND e.groupid = ? AND e.categoryid = ?) OR e.groupid IN (?,?) OR (e.groupid = ? AND e.courseid IN (?,?)) OR (e.groupid = ? AND e.courseid = ? AND e.categoryid != ?)) AND (e.visible = ?) |
ORDER BY COALESCE(e.timesort, e.timestart) ASC, e.id ASC LIMIT ?, ? |
The sub query on the events table aliased as ev causes a full table scan because all of the OR clauses cannot use the same index, changing the query so that it uses UNIONs instead of ORs should allow it to use indexes.
There are also other scenarios in Moodle that use queries that cause full table scans, for example:
- Changing the visibility of an activity
- Deleting an activity
- Backing up an activity
where the query looks something like:
SELECT * FROM mdl_event WHERE modulename = ? AND instance = ? |
