MySql 查询具有大量数据的多个联接

Question

我的数据库中有三个表

表格反馈响应

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

表格反馈_响应_项目

    feed_item_id | resp_id | qn_id | ans_id

表反馈_答案

    ans_id(Primary) | ans_desc | qn_id

我想要的是显示feedback_responses表中的所有行，并且每行包含feedback_response_items中与表feedback_responses主键匹配的9个项目这是resp_id

因此，我将获得所有问题的ans_id，从中我将获得表feedback_answers中响应的答案描述

查询是

$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
        "); 

上面的查询需要很多时间(超过2分钟)来执行如何优化，我对此感到震惊，任何反馈都将非常感激。

Answer 1

我认为，如果您在 select 语句中移动子查询，并加入 items 表，您的查询将运行得非常快! (似乎子查询针对每一行执行，因此需要时间)。这将起作用:

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

希望对您有帮助!