CODEWITHSUNDEEP
mysqljoinquery-optimizationinner-join
Pattatharasu Nataraj
Pattatharasu Nataraj

Reputation: 248

MySql Query with multiple joins with huge data

I have three tables in a database

Table feedback_responses

    resp_id(Primary) | name | mobile | pnr | message | added_on

Table feedback_response_items

    feed_item_id | resp_id | qn_id | ans_id

Table feedback_answers

    ans_id(Primary) | ans_desc | qn_id

What I want is to display all rows in feedback_responses table and each row contains 9 items in feedback_response_items matching the primary key of table feedback_responses which is resp_id

So i will get ans_id of all questions from that i will get answer description of the response in table feedback_answers

and the query is 

$f=$db->Query("
        SELECT fr.resp_id, fr.name, fr.mobile, fr.pnr, fr.message,
        (SELECT ans_id FROM feedback_response_items fr1 WHERE fr.resp_id = fr1.resp_id  AND fr1.qn_id='1') AS qn1Ans,
        (SELECT ans_id FROM feedback_response_items fr2 WHERE fr.resp_id = fr2.resp_id  AND fr2.qn_id='2') AS qn2Ans,
        (SELECT ans_id FROM feedback_response_items fr3 WHERE fr.resp_id = fr3.resp_id  AND fr3.qn_id='3') AS qn3Ans,
        (SELECT ans_id FROM feedback_response_items fr4 WHERE fr.resp_id = fr4.resp_id  AND fr4.qn_id='4') AS qn4Ans,
        (SELECT ans_id FROM feedback_response_items fr5 WHERE fr.resp_id = fr5.resp_id  AND fr5.qn_id='5') AS qn5Ans,
        (SELECT ans_id FROM feedback_response_items fr6 WHERE fr.resp_id = fr6.resp_id  AND fr6.qn_id='6') AS qn6Ans,
        (SELECT ans_id FROM feedback_response_items fr7 WHERE fr.resp_id = fr7.resp_id  AND fr7.qn_id='7') AS qn7Ans,
        (SELECT ans_id FROM feedback_response_items fr8 WHERE fr.resp_id = fr8.resp_id  AND fr8.qn_id='8') AS qn8Ans,
        (SELECT ans_id FROM feedback_response_items fr10 WHERE fr.resp_id = fr10.resp_id  AND fr10.qn_id='10') AS qn10Ans
        FROM feedback_responses fr
        ");

the above query takes lot of time(more than 2 mins) for execution how to do it optimized, i got struck in this any feedback will be really appreciated.

Upvotes: 0

Views: 1670

Answers (2)

Sebastian Paciuk
Sebastian Paciuk

Reputation: 149

I think that if you move your subqueries in the select statement, and JOIN the items table, your query will run very fast! (it seems that the subqueries executes for every row, so it takes time). This will work:

SELECT fr.resp_id, fr.name, fr.mobile, fr.pnr, fr.message,
MAX(IF(fri.qn_id='1',ans_id,0)) AS qn1Ans,
MAX(IF(fri.qn_id='2',ans_id,0)) AS qn2Ans,
MAX(IF(fri.qn_id='3',ans_id,0)) AS qn3Ans,
MAX(IF(fri.qn_id='4',ans_id,0)) AS qn4Ans,
MAX(IF(fri.qn_id='5',ans_id,0)) AS qn5Ans,
MAX(IF(fri.qn_id='6',ans_id,0)) AS qn6Ans,
MAX(IF(fri.qn_id='7',ans_id,0)) AS qn7Ans,
MAX(IF(fri.qn_id='8',ans_id,0)) AS qn8Ans,
MAX(IF(fri.qn_id='10',ans_id,0)) AS qn10Ans,
FROM feedback_responses fr
JOIN feedback_response_items fri ON fr.resp_id = fri.resp_id 
GROUP BY fr.resp_id, fr.name, fr.mobile, fr.pnr, fr.message

I hope it helps!

Upvotes: 1

Booboo
Booboo

Reputation: 44108

This may not be an answer that will fully satisfy you because some programming may be required:

You neglected to specify what indices you have specified for the various table. For table feedback_response_items, you should have defined an index at least on column resp_id (and one on qn_id probably would not hurt). If not, do so and see how that reduces the execution time.

If you already had the index defined or defining the index did not improve the execution time significantly, you need to do some programming. Your SQL should be:

SELECT fr.resp_id, fr.name, fr.mobile, fr.pnr, fr.message, fri.ans_id /*, fa.ans_desc */
FROM feedback_responses fr
JOIN feedback_response_items fri ON fr.resp_id = fri.resp_id    
                                    AND fri.qn_id IN ('1','2','3','4','5','6','7','8','10')
/* JOIN feedback_answers fa on fri.ans_id = fa.ans_id */
ORDER BY fri.qn_id
;

Remove the comments in the above SQL to retrieve the answers also. Now for each value of fr.resp_id, 9 rows will be returned. The intention is for this SQL to be issued by a computer program that will process the groups of 9 rows and display the results as you like.

Upvotes: 1

Related Questions