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:355292
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
