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

fix_course_sortorder performance improvement

XMLWordPrintable

      In function fix_course_sortorder(), this query is a bottleneck on big instances:

          // categories having courses with sortorder duplicates or having gaps in sortorder
      		 
          $sql = "SELECT DISTINCT c1.category AS id , cc.sortorder
      		 
                    FROM {course} c1
      		 
                    JOIN {course} c2 ON c1.sortorder = c2.sortorder
      		 
                    JOIN {course_categories} cc ON (c1.category = cc.id)
      		 
                   WHERE c1.id <> c2.id";
      		 
          $fixcategories = $DB->get_records_sql($sql);

      In one of our Moodles that has over 20k courses, and on my sandbox it takes over 30 seconds to execute. The fix for MySQL is to simply add index on course.sortorder, which brings the execution of the query down to split of a second.

            skodak Petr Skoda
            tmuras Tomasz Muras
            Dan Poltawski Dan Poltawski
            Michael de Raadt Michael de Raadt
            Votes:
            3 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved:

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