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
