-
Bug
-
Resolution: Fixed
-
Minor
-
4.3.3
-
MOODLE_403_STABLE
-
MOODLE_403_STABLE, MOODLE_404_STABLE
-
MDL-81301_403 -
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.
- has a non-specific relationship to
-
MDL-71696 Add question versions
-
- Closed
-