Missing effective index for mdl_questionnaire_resp_single

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:

moodle-mod_questionnaire/classes/responsetype/single.php

Lines 240 to 242 in b06947c

    
           $responsecountsql = 'SELECT COUNT(DISTINCT r.response_id) ' . 
        
               'FROM {' . $this->response_table() . '} r ' . 
        
               'WHERE r.question_id = ? ';

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!

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 !

	$responsecountsql = 'SELECT COUNT(DISTINCT r.response_id) ' .
	'FROM {' . $this->response_table() . '} r ' .
	'WHERE r.question_id = ? ';