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

fix_course_sortorder performance improvement

    XMLWordPrintable

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                3 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  25/Jun/12