I’ve got a huge report for the Medicology MTI that has been running extremely slow due to the amount of data and queries. I’ve been trying to speed it up to make accessing and analysing the results easier.
The users have to answer 56 questions to complete the survey. This means that most for the calculations there’s currently an “if” statement to check they completed all 56 questions and then skip the row of the mysql result set if they haven’t.
I haven’t checked if it will make it quicker but surely checking the amount of answers completed in the SQL query will result in less data and a speed increase. The problem is that the COUNT(*) I’m using to get the number of answers is in the SELECT part of the statement which is executed after the WHERE clause, which is where it needs to be:
SELECT id, COUNT(id) AS cnt FROM answers WHERE cnt>=56 GROUP BY id
The above returns an error. What you need to do is modify the query as follows:
SELECT id, COUNT(id) AS cnt FROM answers WHERE cnt>=56 GROUP BY id HAVING cnt>=56
That will return the required result set.