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
