CODEWITHSUNDEEP
mysqlquery-optimizationsql-optimization
Architect - Hitesh
Architect - Hitesh

Reputation: 1239

How can i optimize this query for sql for counting response

I have a table of response from the question and when i try to count the number of response over the question by query for creating a graph it took 65seconds to load

so can please guide me someone how can we optimize this query 

SELECT
vr.question_id,
(SELECT COUNT(response) FROM visitors_response  WHERE question_id = vr.question_id AND response = 5 ) AS one_star,
(SELECT COUNT(response) FROM visitors_response  WHERE question_id = vr.question_id AND response = 4 ) AS two_star,
(SELECT COUNT(response) FROM visitors_response  WHERE question_id = vr.question_id AND response = 3 ) AS three_star,
(SELECT COUNT(response) FROM visitors_response  WHERE question_id = vr.question_id AND response = 2 ) AS four_star,
(SELECT COUNT(response) FROM visitors_response  WHERE question_id = vr.question_id AND response = 1 ) AS five_star,
(SELECT AVG(response)   FROM visitors_response  WHERE question_id = vr.question_id ) AS average 
FROM visitors_response vr
JOIN questions q ON q.id = vr.question_id 
JOIN survey s ON s.id = q.survey_id
WHERE s.user_id = 101 AND s.status = 'active' 
GROUP BY vr.question_id

Upvotes: 1

Views: 73

Answers (3)

Willem Renzema
Willem Renzema

Reputation: 5187

Note that for any query optimization questions you should provide SHOW CREATE TABLE tablename statements for each table involved in the query.

That said, if the following indexes don't exist, add them to your tables:

survey: (user_id,status)
questions: (survey_id)
visitor_responses: (question_id,response)

The above indexes assume id on the survey and questions table is the primary key of each table, respectively.

Report back on how that improved performance, and include the up to date SHOW CREATE TABLE tablename statements for each of those tables, so we can help make sure you don't have any redundant indexes now.

If performance is not under 1 second, or any other threshold you care to beat, also include a current EXPLAIN plan.

Upvotes: 0

forpas
forpas

Reputation: 164069

Try with conditional aggregation:

SELECT
vr.question_id,
COUNT(CASE WHEN response = 5 THEN response END) AS one_star,
COUNT(CASE WHEN response = 4 THEN response END) AS two_star,
COUNT(CASE WHEN response = 3 THEN response END) AS three_star,
COUNT(CASE WHEN response = 2 THEN response END) AS four_star,
COUNT(CASE WHEN response = 1 THEN response END) AS five_star,
AVG(response) AS average 
FROM visitors_response vr
JOIN questions q ON q.id = vr.question_id 
JOIN survey s ON s.id = q.survey_id
WHERE s.user_id = 101 AND s.status = 'active' 
GROUP BY vr.question_id

Or by using SUM instead of COUNT:

SELECT
vr.question_id,
SUM(response = 5) AS one_star,
SUM(response = 4) AS two_star,
SUM(response = 3) AS three_star,
SUM(response = 2) AS four_star,
SUM(response = 1) AS five_star,
AVG(response) AS average 
FROM visitors_response vr
JOIN questions q ON q.id = vr.question_id 
JOIN survey s ON s.id = q.survey_id
WHERE s.user_id = 101 AND s.status = 'active' 
GROUP BY vr.question_id

Upvotes: 1

artoodetoo
artoodetoo

Reputation: 938

You can use IF() function inside any aggregate function. The trick is COUNT() counts not null values only, so put NULL into "else" part. Like here:

SELECT 
  COUNT(IF(response=1,1,NULL)) AS one_star,
  COUNT(IF(response=2,1,NULL)) AS two_star,
  COUNT(IF(response=3,1,NULL)) AS three_star,
  COUNT(IF(response=4,1,NULL)) AS four_star,
  COUNT(IF(response=5,1,NULL)) AS five_star,
  AVG(response) AS average
FROM visitors_response vr
JOIN questions q ON q.id = vr.question_id 
JOIN survey s ON s.id = q.survey_id
WHERE s.user_id = 101 AND s.status = 'active' 
GROUP BY vr.question_id

or you can do the same via "OR" operation:

  COUNT(response=1 OR NULL) AS one_star,

As for me, it is the shortest and most understandable option.

Upvotes: 0

Related Questions