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

Reduce the number of full table scans on the event table




      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,
                                  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 = ?


        Issue Links



              nmagill Neill Magill
              nmagill Neill Magill
              Simey Lameze Simey Lameze
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              CiBoT CiBoT
              Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo, Matteo Scaramuccia, Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo
              1 Vote for this issue
              8 Start watching this issue



                Time Tracking

                  Original Estimate - Not Specified
                  Not Specified
                  Remaining Estimate - 0 minutes
                  Time Spent - 2 hours, 15 minutes
                  2h 15m