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

Reduce the number of full table scans on the event table

    XMLWordPrintable

    Details

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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nmagill Neill Magill
              Reporter:
              nmagill Neill Magill
              Peer reviewer:
              Simey Lameze
              Integrator:
              Eloy Lafuente (stronk7)
              Tester:
              CiBoT
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                15/Jun/20

                  Time Tracking

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