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

Deleting records from the mdl_question_set_references table takes too long

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: Minor Minor
    • 4.3.5, 4.4.1
    • 4.3.3
    • Quiz

      For large pages there is a problem with deleting records from the mdl_question_set_references table, this process takes a very long time.

      For a table with about 1 million records, selecting one row takes 2.54 sec.

      mysql> select * FROM mdl_question_set_references WHERE itemid = '294878' AND component = 'mod_quiz' AND questionarea = 'slot';
      +--------+----------------+-----------+--------------+--------+--------------------+---------------------------------------------------------+
      | id     | usingcontextid | component | questionarea | itemid | questionscontextid | filtercondition                                         |
      +--------+----------------+-----------+--------------+--------+--------------------+---------------------------------------------------------+
      | 280450 |          80979 | mod_quiz  | slot         | 294878 |                  1 | {"questioncategoryid":16328,"includingsubcategories":1} |
      +--------+----------------+-----------+--------------+--------+--------------------+---------------------------------------------------------+
      1 row in set (2.54 sec)

       

      I reproduced this situation on a clean Moodle 4.3.3 instance.

      I added over 3,000 random questions to the quiz.
      The mdl_question_set_references table also contained other random records (over 500k in total).

      After deleting the quiz, I ran the adhoc task, it ran for over an hour:

      Execute adhoc task: core_course\task\course_delete_modules Adhoc task id: 5 Adhoc task custom data: {"cms":[{"id":"6","course":"2","module":"17","instance":"4","section":"2","idnumber":"","added":"1710841513","score":"0","indent":"0","visible":"1","visibleoncoursepage":"1","visibleold":"1","groupmode":"0","groupingid":"0","completion":"0","completiongradeitemnumber":null,"completionview":"0","completionexpected":"0","completionpassgrade":"0","showdescription":"0","availability":null,"deletioninprogress":"1","downloadcontent":"1","lang":""}],"userid":"2","realuserid":"2"} ... started 13:07:02. Current memory use 1.8 MB. ... used 16606 dbqueries ... used 3653.2725460529 seconds Adhoc task complete: core_course\task\course_delete_modules 

      In this case, deleting one record took over 1 sec.

       

      The proposed solution is to add an index to the itemid column.

      after adding the index:

      mysql> select * FROM mdl_question_set_references WHERE itemid = '294878' AND component = 'mod_quiz' AND questionarea = 'slot';
      +--------+----------------+-----------+--------------+--------+--------------------+---------------------------------------------------------+
      | id     | usingcontextid | component | questionarea | itemid | questionscontextid | filtercondition                                         |
      +--------+----------------+-----------+--------------+--------+--------------------+---------------------------------------------------------+
      | 280450 |          80979 | mod_quiz  | slot         | 294878 |                  1 | {"questioncategoryid":16328,"includingsubcategories":1} |
      +--------+----------------+-----------+--------------+--------+--------------------+---------------------------------------------------------+
      1 row in set (0.00 sec) 

      the issue can be reproduced as follows:

      • create 1 course
      • create 1 quiz
      • create and add 10 random questions into the quiz
      • fill up mdl_question_set_references table with random / generated values - e.g. 500k records
      • delete quiz
        This should trigger slow DELETE FROM mdl_question_set_references queries.

            timhunt Tim Hunt
            esoroka Ewa Soroka
            Jordi Pujol-Ahulló Jordi Pujol-Ahulló
            Huong Nguyen Huong Nguyen
            CiBoT CiBoT
            Votes:
            1 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 hours, 40 minutes
                2h 40m

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