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

        1. explain-for-uonion-conditions.sql
          2 kB
          Neill Magill
        2. slow-queries.png
          61 kB
          Neill Magill
        3. top-time-consuming-query-types.png
          32 kB
          Neill Magill

            nmagill Neill Magill
            nmagill Neill Magill
            Simey Lameze Simey Lameze
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            CiBoT CiBoT
            1 Vote for this issue
            8 Start watching this issue


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

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.