MySql Query with multiple joins with huge data

Question

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.

Answer 1

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!