-
Bug
-
Resolution: Unresolved
-
Minor
-
None
-
4.1.5
-
MOODLE_401_STABLE
We have encountered an issue in which downloading CSV on the Quiz Results Page is failing when selecting Enrolled users who have, or have not, attempted the quiz. The following SQL is taking 20minutes to execute:
The quiz has 2 questions only with about 40+ attempts.
SELECT
|
qas.id,
|
qa.id AS questionattemptid,
|
qa.questionusageid,
|
qa.slot,
|
qa.behaviour,
|
qa.questionid,
|
qa.variant,
|
qa.maxmark,
|
qa.minfraction,
|
qa.maxfraction,
|
qa.flagged,
|
qa.questionsummary,
|
qa.rightanswer,
|
qa.responsesummary,
|
qa.timemodified,
|
qas.id AS attemptstepid,
|
qas.sequencenumber,
|
qas.state,
|
qas.fraction,
|
qas.timecreated,
|
qas.userid
|
|
|
FROM (
|
SELECT DISTINCT quiza.uniqueid
|
FROM mdl_user u
|
JOIN mdl_user_info_field uf1f_1 ON LOWER(uf1f_1.shortname) COLLATE utf8mb4_bin = LOWER('section') |
LEFT JOIN mdl_user_info_data uf1d_1 ON uf1d_1.fieldid = uf1f_1.id
|
AND uf1d_1.userid = u.id JOIN mdl_user_info_field uf1f_2 ON LOWER(uf1f_2.shortname) COLLATE utf8mb4_bin = LOWER('subgroup') |
LEFT JOIN mdl_user_info_data uf1d_2 ON uf1d_2.fieldid = uf1f_2.id
|
AND uf1d_2.userid = u.id JOIN mdl_user_info_field uf1f_3 ON LOWER(uf1f_3.shortname) COLLATE utf8mb4_bin = LOWER('affiliation') |
LEFT JOIN mdl_user_info_data uf1d_3 ON uf1d_3.fieldid = uf1f_3.id
|
AND uf1d_3.userid = u.id JOIN mdl_user_info_field uf1f_4 ON LOWER(uf1f_4.shortname) COLLATE utf8mb4_bin = LOWER('department') |
LEFT JOIN mdl_user_info_data uf1d_4 ON uf1d_4.fieldid = uf1f_4.id
|
AND uf1d_4.userid = u.id
|
LEFT JOIN mdl_quiz_attempts quiza ON
|
quiza.userid = u.id AND quiza.quiz = '202045' |
JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = u.id
|
JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = '195153') |
JOIN (SELECT DISTINCT userid
|
FROM mdl_role_assignments
|
WHERE contextid IN (1,129008,129109,129150,3906617,4035964,4512544) |
AND roleid IN (5,12,21,22,6,17,16) |
) ra ON ra.userid = u.id
|
WHERE (quiza.preview = 0 OR quiza.preview IS NULL) AND 1 = 1 AND u.deleted = 0 AND u.id <> '1' AND u.deleted = 0 |
) quizasubquery
|
JOIN mdl_question_attempts qa ON qa.questionusageid = quizasubquery.uniqueid
|
JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id
|
AND qas.sequencenumber = (
|
SELECT MAX(sequencenumber)
|
FROM mdl_question_attempt_steps
|
WHERE questionattemptid = qa.id
|
)
|
|
|
WHERE
|
1 = 1 |
AND qa.slot IN ('1','2') |
The above slowness seems to be caused by the last clause "AND qa.slot IN ('1','2'). It appears that it is performing a full table scan at table mdl_question_attempts, which in our case has about 65M records.
Removing/commenting that line executes the query under 1 second.
I have attempted to get the code which generates the SQL above and it looks to be the code at question/engine/datalib.php function load_questions_usages_latest_steps.
