Hi, I am sure there must be a way to restructure this query to bring the time down but i cannot see it. Any pointers at all would be greatly appreciated.
> Hi, > > We have a problem with one of our MySQL statements and wondering if > you guys can help point us in the right direction. > > Basically the following statement is taking 5 seconds to run. We have > diagnosed it is down to the join of two select statement. When the > select statements are run individually they take only 0.2 seconds but > when combined with the JOIN it takes 5 seconds. > > WE have been told then when MySQL performs a join it creates temporary > tables in the background. Is this correct? > > Is there anything you can see that we are doing wrong or can you see a > better way? > > -------- code start ------ > > SELECT temp_4.primaryid, temp_1.`subjectID` , temp_4.`testOccasionID` , > `studyNumbers` ,`testDate` > > FROM ( > SELECT * FROM ( > SELECT primarys.primaryid , q_1 AS `subjectID` , q_2 AS `studyNumbers` > FROM primarys LEFT OUTER JOIN questions_1_100 ON primarys.primaryid = > questions_1_100.primaryid WHERE 0 = 0 AND q_1 IS NOT NULL GROUP BY > primaryid) AS maintable_1 > GROUP BY `subjectID` ) AS temp_1 > > JOIN > > (SELECT * FROM > (SELECT primarys.primaryid , q_1 AS `subjectID` , q_4 AS > `testOccasionID` , DATE_FORMAT(q_5, '%m/%d/%Y') AS `testDate` FROM > primarys LEFT OUTER JOIN questions_1_100 ON primarys.primaryid = > questions_1_100.primaryid WHERE 0 = 0 AND q_1 IS NOT NULL AND q_4 IS > NOT NULL GROUP BY primaryid) AS maintable_4 > GROUP BY `subjectID` ,`testOccasionID` ) AS temp_4 > > ON temp_1.`subjectID` = temp_4.`subjectID` > > -------- code end ------ > > Many thanks > Richard ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355601 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm