search

PoetOS / moodle-mod_questionnaire

Questionnaire module for Moodle - Contributed module
GNU General Public License v3.0
79 stars 182 forks source link

Missing effective index for mdl_questionnaire_resp_single #564

Open yuttie opened 6 months ago

yuttie commented 6 months ago

Thank you for developing and maintaining a great plugin!

We have noticed that certain type of queries such as SELECT COUNT(DISTINCT r.response_id) FROM mdl_questionnaire_resp_single r WHERE r.question_id = '12345' are identified as slow queries by our MySQL database and they actually affect the performance of our system badly.

It seems that these queries are generated at: https://github.com/PoetOS/moodle-mod_questionnaire/blob/b06947cbe9e46844bdf9728ee3e93d9e328d6344/classes/responsetype/single.php#L240-L242

I examined the indexes created for the table and found that there is no effective index for the query. There is indeed an index mdl_quesrespsing_resque_ix but it's column order is (response_id, question_id) but, in my opinion, it should be (question_id, response_id) or just (question_id) because the query needs to first find records where question_id is a given number and then group by response_id.

I've tested whether adding an index helps or not. The following index greatly improved the performance; the above query, which took 0.76 seconds previously, now only takes 0.00 seconds.

CREATE INDEX test ON mdl_questionnaire_resp_single (question_id);

We have around 4,740,000 records in mdl_questionnaire_resp_single table.

Thanks for your help!

brian-winstead commented 6 months ago

I agree that this index offers a great improvement. We added the index to a site where the questionnaire response summary page was taking over two minutes to load for a particular questionnaire and now the page loads in under 5 seconds. Thanks for the suggestion @yuttie !