You could start by replacing SELECT * with SELECT column1, column2, column3, etc.
On 4/26/13 6:56 AM, "Richard White" <rich...@re-base.net> wrote: > >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:355606 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
