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

Extremely slow query in calendar for large sites

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Set the calendar_adminseesall config setting to true
      Visit a calendar page
      Ensure that all entries are correctly shown

      Run the old query and the new query to ensure that the same results are always returned. For convenience, I've pasted them below:

      Old code:

      SELECT DISTINCT
      		 
          c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
      		 
      FROM mdl_course c
      		 
      JOIN mdl_event e ON e.courseid = c.id
      		 
      LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
      		 
      ;

      New code:

      SELECT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
      		 
      FROM mdl_course c
      		 
      LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
      		 
      WHERE EXISTS (SELECT 1 FROM mdl_event e WHERE e.courseid = c.id)
      		 
      ;

      Show
      Set the calendar_adminseesall config setting to true Visit a calendar page Ensure that all entries are correctly shown Run the old query and the new query to ensure that the same results are always returned. For convenience, I've pasted them below: Old code: SELECT DISTINCT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance FROM mdl_course c JOIN mdl_event e ON e.courseid = c.id LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50) ; New code: SELECT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance FROM mdl_course c LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50) WHERE EXISTS (SELECT 1 FROM mdl_event e WHERE e.courseid = c.id) ;
    • Difficulty:
      Moderate
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-32340-master-2
    • Pull Master Diff URL:

      Description

      It seems MDL-31086 introduces a very slow query in calendar, for large sites.

      $sql = "SELECT DISTINCT c.* $select
      		 
      FROM {course} c
      		 
      JOIN {event} e ON e.courseid = c.id
      		 
      $join";

      In my case, after upgrading to 2.1.5 via CLI, I could not login to the site as an admin due to hitting 5 minute connection timeout. Checking postgres showed this query running for several minutes - it appears to try and sort by all columns in the query, then return the top 20 for the get_records_sql limit. (So, not a very good plan, but...)

      Since we know course id is unique enough, it is much nicer on the database to advise it only to make the results unique by c.id i.e:

      -        $sql = "SELECT DISTINCT c.* $select
      		 
      +        $sql = "SELECT DISTINCT ON (c.id) c.* $select

      This makes the query return in only 10ms for this site. I'm not sure if this is compatible enough syntax for core, however.

      Github incoming.

        Attachments

          Issue Links

          is a regression caused by

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-31086 invalid sql in calendar/lib.php

          • Minor - Minor loss of function where workaround is possible
          • Closed

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  9/Jul/12