Re: Optimising SQL Statement
On Fri, Apr 26, 2013 at 12:56 PM, Richard White wrote: > I am sure there must be a way to restructure this query to bring the time > down > I am afraid we can't really help because most of the information we need for that is missing. Schema, cardinalities etc. > 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` > In addition there is a serious issue with this part of the query: it is not deterministic. You are selecting the columns primaryid, subjectID and studyNumbers from the inner select and then grouping by subjectID, without telling the DB what to do for the other columns. So if your inner query produces: 1, 2, 3 3, 2, 1 The result could be either of: 1,2,3 3,2,1 I am presuming this query produces the results you are expecting, but that is either an accident (and as soon as an optimisation changes the execution plan you get different results), or because there is a lot of correlation between the columns of your tables, of which you haven't told us anything. Jochem ~| 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:355619 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Optimising SQL Statement
You could start by replacing SELECT * with SELECT column1, column2, column3, etc. On 4/26/13 6:56 AM, "Richard White" 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
Re: Optimising SQL Statement
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
Optimising SQL Statement
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
