Hi,
We are having some problems related to performance when it comes to quizzes (regrade, attempt, show grades).
When trying to show grades at the same time of a regrade we get very high load from the server. Searching on mysql-slow-queries.log I could see the query that took a long time to execute at that time. It examined about 111 millions (?) rows!
I would like to know if this is a normal number of rows examined and what procedure should we follow to improve server performance in these cases.
Thank you,
susana
-----------
# Query_time: 283 Lock_time: 0 Rows_sent: 30 Rows_examined: 111344886
SELECT CONCAT(u.id,'#', IFNULL(qa.attempt, 0) ) AS uniqueid, qa.uniqueid AS attemptuniqueid, qa.id AS attempt, u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt, qa.sumgrades, qa.timefinish, qa.timestart, qa.timefinish - qa.timestart AS duration , qs25987.grade AS qsgrade25987, qs25987.event AS qsevent25987, qs25987.id AS qsid25987 FROM mdl_user u LEFT JOIN mdl_quiz_attempts qa ON qa.userid = u.id AND qa.quiz = 984 LEFT JOIN mdl_question_sessions qns25987 ON qns25987.attemptid = qa.uniqueid AND qns25987.questionid = 25987 LEFT JOIN mdl_question_states qs25987 ON qs25987.id = qns25987.newgraded WHERE qa.id IS NOT NULL AND qa.preview = 0 ORDER BY qsgrade25987 ASC LIMIT 30;
------------
Well, the query includes the question_states table, which can get quite big.
Anyway, what you need to do is take that query, and do an EXPLAIN on it (http://dev.mysql.com/doc/refman/5.0/en/explain.html), to try to work out what is going on.
Anyway, what you need to do is take that query, and do an EXPLAIN on it (http://dev.mysql.com/doc/refman/5.0/en/explain.html), to try to work out what is going on.