CODEWITHSUNDEEP
mysqlsqldatabase
Matthew Smart
Matthew Smart

Reputation: 1319

make mysql query more efficient

so at the minute my initial query looks like this:

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`

Now i have read around and this is a very weak lengthy query that could be optimised.

Does any one know how i could do this? my attempt failed to provide the same result which is below:

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

EDIT::

Okay so after looking at your comments i can see why, silly mistake.

A few of the joins are actually joining two tables together instead of joining them to the FROM table (survey).

This is my new attempt which has failed.

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

The error i get is : Not unique table/alias: 'surveyQuestions'

Upvotes: 1

Views: 103

Answers (1)

PaulF
PaulF

Reputation: 6773

Try this:

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

Upvotes: 1

Related Questions