Badly performing MySQL queries connected to questions

1. Use a real database like Postgres. 2. Or this code change will fix the problem for MySQL users, although there are issues with the code that we can't use it in the standard Moodle release. The problem is in code /question/engine/datalib.php For example starting at line 662: $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN ( SELECT qas.id FROM {question_attempts} qa JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id WHERE $where)", $params); Why this MySQL query is slow: http://forums.mysql.com/read.php?24,5240,5240 Updated code, that works much faster: $stepids = $this->db->get_fieldset_sql(" SELECT qas.id FROM {question_attempts} qa JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id WHERE $where", $params); if (!empty($stepids)) { list($sqlin, $delparams) = $this->db->get_in_or_equal($stepids); $this->db->delete_records_select('question_attempt_step_data', "attemptstepid $sqlin", $delparams); } Similar problems also at lines: 668-671, 696-699. datalib.php file, with this fix: https://github.com/mangus/moodle/blob/7259b04e27ec4e5d03539f3355791ab451e3fe0c/question/engine/datalib.php

You need a database that already contains many quiz attempts. See some of the table sizes quoted in comments below. You also need to test this with MySQL (to verify the new code) and with another DB (to verify there are no regressions). 1. Create a quiz 2. Attempt it several times as different students. 3. Go to the quiz reports, select all attempts, and delete them. 4. Verify that it does not take too long, and that only the right quiz attempts are deleted. (You can probably do that by doing a row count of all the question_attempt* tables, and verifying that that after creating a lot of quiz attempts, and then deleting them, you get back to exactly the same number of rows. Of course, this requires no one else to be using quizzes on the server at the same time.)