Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 3.7.9
-
Fix Version/s: None
-
Component/s: Database SQL/XMLDB
-
Labels:None
-
Affected Branches:MOODLE_37_STABLE
Description
We are using moodle 3.7.2 and in some cases retaking a quiz takes too long to initialize. I have narrowed the issue on a specific query that takes too long to execute.
The query in question is when a user tries to retake the quiz and it looks like mysql uses the wrong index for the same query just different user.
//MariaDB [ssio_qa]> explain SELECT q.id, (SELECT COUNT(1)
|
-> FROM mdl_quiz_attempts quiza
|
-> JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid
|
-> WHERE qa.questionid = q.id AND quiza.quiz = '5441' AND quiza.userid = '207883' AND preview = 0 AND state IN ('finished', 'abandoned') |
-> ) AS previous_attempts
|
-> FROM mdl_question q
|
-> WHERE q.category IN ('9400','9483','9484','9401','9485','9486','9487') |
-> AND q.parent = 0 |
-> AND q.hidden = 0 AND (q.qtype NOT IN ('description','missingtype','random','randomsamatch')) |
-> ORDER BY previous_attempts;
|
+------+--------------------+-------+--------+-------------------------------------------------------------------------------------+---------------------+---------+----------------------------+------+---------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+--------+-------------------------------------------------------------------------------------+---------------------+---------+----------------------------+------+---------------------------------------------------------------------+
|
| 1 | PRIMARY | q | range | mdl_ques_catidn_uix,category,mdl_ques_par_ix,mdl_ques_qty_ix | mdl_ques_catidn_uix | 8 | NULL | 45 | Using index condition; Using where; Using temporary; Using filesort | |
| 2 | DEPENDENT SUBQUERY | qa | ref | mdl_quesatte_queslo_uix,mdl_quesatte_que_ix,mdl_quesatte_que2_ix | mdl_quesatte_que_ix | 8 | ssio_qa.q.id | 231 | | |
| 2 | DEPENDENT SUBQUERY | quiza | eq_ref | uniqueid,mdl_quizatte_quiuseatt_uix,quiz,mdl_quizatte_use_ix,mdl_quizatte_statim_ix | uniqueid | 8 | ssio_qa.qa.questionusageid | 1 | Using where | |
+------+--------------------+-------+--------+-------------------------------------------------------------------------------------+---------------------+---------+----------------------------+------+---------------------------------------------------------------------+
|
3 rows in set (0.046 sec) |
|
|
MariaDB [ssio_qa]> explain SELECT q.id, (SELECT COUNT(1) |
-> FROM mdl_quiz_attempts quiza
|
-> JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid
|
-> WHERE qa.questionid = q.id AND quiza.quiz = '5441' AND quiza.userid = '247681' AND preview = 0 AND state IN ('finished', 'abandoned') |
-> ) AS previous_attempts
|
-> FROM mdl_question q
|
-> WHERE q.category IN ('9400','9483','9484','9401','9485','9486','9487') |
-> AND q.parent = 0 |
-> AND q.hidden = 0 AND (q.qtype NOT IN ('description','missingtype','random','randomsamatch')) |
-> ORDER BY previous_attempts;;
|
+------+--------------------+-------+-------+-------------------------------------------------------------------------------------+----------------------------+---------+------------------------+------+----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+-------+-------------------------------------------------------------------------------------+----------------------------+---------+------------------------+------+----------------------------------------------------+
|
| 1 | PRIMARY | q | range | mdl_ques_catidn_uix,category,mdl_ques_par_ix,mdl_ques_qty_ix | mdl_ques_catidn_uix | 8 | NULL | 45 | Using index condition; Using where; Using filesort | |
| 2 | DEPENDENT SUBQUERY | quiza | ref | uniqueid,mdl_quizatte_quiuseatt_uix,quiz,mdl_quizatte_use_ix,mdl_quizatte_statim_ix | mdl_quizatte_quiuseatt_uix | 16 | const,const | 2 | Using where | |
| 2 | DEPENDENT SUBQUERY | qa | ref | mdl_quesatte_queslo_uix,mdl_quesatte_que_ix,mdl_quesatte_que2_ix | mdl_quesatte_queslo_uix | 8 | ssio_qa.quiza.uniqueid | 11 | Using where | |
+------+--------------------+-------+-------+-------------------------------------------------------------------------------------+----------------------------+---------+------------------------+------+----------------------------------------------------+
|
3 rows in set (0.032 sec) |
|
The query for the first user returns results fast but for the second user it hanges.
If i force the usage of the same index on a test query for the slow user query i get results right away.
// SELECT q.id, (SELECT COUNT(1)
|
FROM mdl_quiz_attempts quiza use index (mdl_quizatte_quiuseatt_uix)
|
JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid
|
WHERE qa.questionid = q.id AND quiza.quiz = '5441' AND quiza.userid = '207883' AND preview = 0 AND state IN ('finished', 'abandoned') |
) AS previous_attempts
|
FROM mdl_question q
|
WHERE q.category IN ('9416','9536','9537','9538','9539','9540','9541') |
AND q.parent = 0 |
AND q.hidden = 0 AND (q.qtype NOT IN ('description','missingtype','random','randomsamatch')) |
ORDER BY previous_attempts;
|
Does anyone have an idea how to fix/optimize this error? Is it the moodle job to define usage of indexes or is this a bad mysql configuration. This happens only for a small subset of users.