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

Improve removal of course module completion data

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Notes

      • This issue must be tested against MySQL or MariaDB, and Postgres (i.e. MySQL and Postgres, or MariaDB and Postgres)

      Setup

      1. Have a look at your config.php and locate the dbprefix. Make a note of it.
      2. Run the following SQL in your database (replace 'mdl_' with the prefix in use), and make a note of the resulting number:

        SELECT COUNT(1) FROM mdl_course_modules_completion;
        

      3. Make a note of the result.
        We will re-run this query a number of times and we'll need this

      Before applying the patch

      1. Enable completion tracking on your Moodle site:
        1. Login as admin
        2. Navigate to Site administration -> Advanced features
        3. Enable completion tracking
      2. Create three new courses (with completion enabled).
      3. Create a new assignment in each course
        1. Under "Activity completion" set "Completion tracking" to "Students can manually mark the activity as completed"
      4. Duplicate the assignment twice in each course (Each course now has three assignments)
      5. Enrol a student in each course
      6. Log in as the test student account and mark each of the activities complete.
      7. Run the following SQL, using the original result you had earlier:

        SELECT COUNT(1) - [resultfromsetup] FROM mdl_course_modules_completion;
         
        -- For example:
        SELECT COUNT(1) - 308 FROM mdl_course_modules_completion;
        

        1. Confirm that the result is 9
      8. Log back in as admin
      9. Delete one of the assignments in each course
      10. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result is 6
      11. Delete one of the courses
      12. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result has not changed and is still 6

      Apply the patch

      1. Ensure that you're on the latest version of the code for your branch
      2. Run the Moodle upgrade:

        php admin/cli/upgrade.php
        

      3. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result has not changed and is still 4
      4. Delete one of the remaining courses.
      5. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result has not changed and is still 2
      Show
      Notes This issue must be tested against MySQL or MariaDB, and Postgres (i.e. MySQL and Postgres, or MariaDB and Postgres) Setup Have a look at your config.php and locate the dbprefix . Make a note of it. Run the following SQL in your database (replace 'mdl_' with the prefix in use), and make a note of the resulting number: SELECT COUNT(1) FROM mdl_course_modules_completion; Make a note of the result. We will re-run this query a number of times and we'll need this Before applying the patch Enable completion tracking on your Moodle site: Login as admin Navigate to Site administration -> Advanced features Enable completion tracking Create three new courses (with completion enabled). Create a new assignment in each course Under "Activity completion" set "Completion tracking" to "Students can manually mark the activity as completed" Duplicate the assignment twice in each course (Each course now has three assignments) Enrol a student in each course Log in as the test student account and mark each of the activities complete. Run the following SQL, using the original result you had earlier: SELECT COUNT(1) - [resultfromsetup] FROM mdl_course_modules_completion;   -- For example: SELECT COUNT(1) - 308 FROM mdl_course_modules_completion; Confirm that the result is 9 Log back in as admin Delete one of the assignments in each course Run the SQL again using the same value - i.e. in the example 308 Confirm that the result is 6 Delete one of the courses Run the SQL again using the same value - i.e. in the example 308 Confirm that the result has not changed and is still 6 Apply the patch Ensure that you're on the latest version of the code for your branch Run the Moodle upgrade: php admin/cli/upgrade.php Run the SQL again using the same value - i.e. in the example 308 Confirm that the result has not changed and is still 4 Delete one of the remaining courses. Run the SQL again using the same value - i.e. in the example 308 Confirm that the result has not changed and is still 2
    • Affected Branches:
      MOODLE_38_STABLE
    • Fixed Branches:
      MOODLE_38_STABLE, MOODLE_39_STABLE
    • Pull 3.9 Branch:
      MDL-69687-m39
    • Pull 3.10 Branch:
      MDL-69687-m310
    • Pull Master Branch:
      MDL-69687-master

      Description

      The query used to delete course module completion data from a course is inefficient.

      The current query which can be found within the remove_course_contents function in /lib/moodlelib.php at lines 5327-5329 relies upon a subquery:

      DELETE FROM mdl_course_modules_completion WHERE coursemoduleid IN (SELECT id FROM mdl_course_modules WHERE course=?)

      The subquery approach is handy as it works across data engines but is very inefficient.  An explain plan on MySQL show the query requiring a full table scan on mdl_course_modules_completion.  On a database with about 2.1 million records in the mdl_course_modules_completion table, the delete statement frequently takes over 60 seconds to run.

      By comparison using a join allows the query to use indexes on both database tables, mdl_courmodu_cou_ix and mdl_courmoducomp_cou_ix. 

      DELETE cmc
      FROM mdl_course_modules_completion cmc
      INNER JOIN mdl_course_modules cm ON cm.id = cmc.coursemoduleid
      WHERE cm.course = 180;

      I believe the above syntax would also be valid on Microsoft SQL Server.  PostgreSQL would require different syntax:

      DELETE from mdl_course_modules_completion cmc
      USING mdl_course_modules cm
      WHERE cm.id = cmc.coursemoduleid
      AND cm.course = ?

      I am less familiar with PostgreSQL to say if this approach is any better there.  On a very small test database, I get the same explain plan with the above query as with the original version with the subquery.

        Attachments

        1. image-2020-10-19-14-58-42-109.png
          image-2020-10-19-14-58-42-109.png
          90 kB
        2. mysql.jpg
          mysql.jpg
          55 kB
        3. postgres.jpg
          postgres.jpg
          65 kB

          Issue Links

            Activity

              People

              Assignee:
              quen Sam Marshall
              Reporter:
              brian.winstead Brian Winstead
              Peer reviewer:
              Tim Hunt Tim Hunt
              Integrator:
              Andrew Lyons Andrew Lyons
              Tester:
              Janelle Barcega Janelle Barcega
              Participants:
              Component watchers:
              Sam Marshall, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona)
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                9/Nov/20

                  Time Tracking

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