- Issue created by @nguyenphan
- 🇻🇳Vietnam nguyenphan Hanoi, Vietnam
I make patch 3536776-performance-optimization-quiz_question_relationship.patch. Please review it.
Problem/Motivation
In the current implementation of the Quiz::buildLayout() method (within quiz/src/Entity/Quiz.php), there's a SQL query used to fetch quiz questions and sort them by parent/child hierarchy.
This query includes a LEFT JOIN with an OR condition:
LEFT JOIN {quiz_question_relationship} qqr2
ON (qqr.qqr_pid = qqr2.qqr_id OR (qqr.qqr_pid IS NULL AND qqr.qqr_id = qqr2.qqr_id))
This causes performance issues in large datasets (e.g., 60,000+ records), as verified using EXPLAIN which reports
Proposed resolution
Update the query in Quiz::buildLayout() to use a simpler LEFT JOIN and COALESCE() function to preserve logic without OR.
🔧 Optimized version:
$query = \Drupal::database()->query('
SELECT qqr.question_id as qqid, qqr.question_vid as vid, qq.type, qqr.qqr_id, qqr.qqr_pid, qq.title,
COALESCE(qqr2.weight, qqr.weight) AS parent_weight
FROM {quiz_question_relationship} qqr
JOIN {quiz_question} qq ON qqr.question_id = qq.qqid
LEFT JOIN {quiz_question_relationship} qqr2 ON qqr.qqr_pid = qqr2.qqr_id
WHERE qqr.quiz_vid = :quiz_vid
AND qqr.question_status = :question_status
ORDER BY parent_weight, qqr.weight
', [
':quiz_vid' => $this->getRevisionId(),
':question_status' => QuizQuestion::QUESTION_ALWAYS,
]);
Active
7.0
Code - Quiz core
I make patch 3536776-performance-optimization-quiz_question_relationship.patch. Please review it.