mysql - 让mysql查询更高效

Question

所以此时我的初始查询如下所示:

SELECT 
    `surveyEntries`.`ID` AS EntryID,
    `surveyEntries`.`created` AS EntryDate,
    `hw_services`.`name` AS Provider, 
    `surveyQuestions`.`ID` AS QuestionID,
    `surveyQuestions`.`label` AS Question, 
    `survey_meta`.`answer` AS Answer,
    `surveyQuestions`.`parentID` AS ParentQuestion
FROM `survey` , `surveyQuestions` , `surveyEntries` , `survey_meta` , `hw_services`
WHERE `survey`.`ID` =2
    AND `survey`.`ID` = `surveyQuestions`.`surveyID`
    AND `survey`.`ID` = `surveyEntries`.`surveyID`
    AND `surveyEntries`.`ID` = `survey_meta`.`entryID`
    AND `hw_services`.`ID` = `surveyEntries`.`hw_serviceID`
    AND `surveyQuestions`.`ID` = `survey_meta`.`questionID` 

现在我已经阅读过，这是一个可以优化的非常弱的冗长查询。

有人知道我该怎么做吗？我的尝试未能提供以下相同的结果:

SELECT 
    `surveyEntries`.`ID` AS EntryID,
    `surveyEntries`.`created` AS EntryDate,
    `hw_services`.`name` AS Provider,
    `surveyQuestions`.`ID` AS QuestionID,
    `surveyQuestions`.`label` AS Question,
    `survey_meta`.`answer` AS Answer,
    `surveyQuestions`.`parentID` AS ParentQuestion
FROM `survey`
    JOIN `surveyQuestions` 
        ON `survey`.`ID` = `surveyQuestions`.`surveyID`
    JOIN `surveyEntries`
        ON `survey`.`ID` = `surveyEntries`.`surveyID`
    JOIN `survey_meta`
        ON `survey`.`ID` = `survey_meta`.`entryID`
    JOIN `hw_services` 
        ON `survey`.`ID` = `hw_services`.`ID`
WHERE `survey`.`ID` =2

编辑::

好吧，在看了你的评论后我明白了为什么，愚蠢的错误。

一些连接实际上是将两个表连接在一起，而不是将它们连接到 FROM 表(调查)。

这是我的新尝试，但失败了。

SELECT 
`surveyEntries`.`ID` AS EntryID,
`surveyEntries`.`created` AS EntryDate,
`hw_services`.`name` AS Provider,
`surveyQuestions`.`ID` AS QuestionID,
`surveyQuestions`.`label` AS Question,
`survey_meta`.`answer` AS Answer,
`surveyQuestions`.`parentID` AS ParentQuestion
FROM `survey`
JOIN `surveyQuestions` 
    ON `survey`.`ID` = `surveyQuestions`.`surveyID`

JOIN `surveyEntries`
    ON `survey`.`ID` = `surveyEntries`.`surveyID`

JOIN `survey_meta`
    ON `surveyEntries`.`surveyID` = `survey_meta`.`entryID`

JOIN `hw_services` 
    ON `hw_services`.`ID` = `surveyEntries `.`hw_serviceID`

JOIN `surveyQuestions` 
    ON `surveyQuestions`.`ID` = `survey_meta`.`questionID`

WHERE `survey`.`ID` =2 

我得到的错误是:不是唯一的表/别名:'surveyQuestions'

Answer 1

试试这个:

SELECT 
`surveyEntries`.`ID` AS EntryID,
`surveyEntries`.`created` AS EntryDate,
`hw_services`.`name` AS Provider,
`surveyQuestions`.`ID` AS QuestionID,
`surveyQuestions`.`label` AS Question,
`survey_meta`.`answer` AS Answer,
`surveyQuestions`.`parentID` AS ParentQuestion
FROM `survey`
JOIN `surveyQuestions` 
    ON `survey`.`ID` = `surveyQuestions`.`surveyID`
JOIN `surveyEntries`
    ON `survey`.`ID` = `surveyEntries`.`surveyID`
JOIN `survey_meta`
     ON (`surveyEntries`.`ID` = `survey_meta`.`entryID` AND `surveyQuestions`.`ID` = `survey_meta`.`questionID`)
JOIN `hw_services` 
    ON `surveyEntries`.`hw_serviceID` = `hw_services`.`ID`
WHERE `survey`.`ID` =2