James Holmes wrote: > > The joins are all straightforward and the tables contain what they sound > like they contain. It takes 4.3 seconds (or so) to run. I'll send the > explain output later on if necessary.
Please do so. I expect that it will show that the joined version is driven by the wrklocations table and the subquery version is driven by the wrkcalcs table. > The end result is the same dataset. I did notice that the subquery > version allows for one less group by expression, which may or not make > the difference. I don't think that explains the difference. How fast is this query? And what does the explain output show? SELECT escunits.unitname, SUM (wrkcalcs.score)as WDMSCORE, wrkenrolments.eftsu, wrkperiods.periodname, wrklocations.LOCATION FROM wrklocations, escunits, wrkcalcs, wrkperiods, wrkenrolments WHERE (wrklocations.locationid = wrkcalcs.locationid) AND (wrkperiods.periodid = wrkcalcs.periodid) AND (escunits.unitid = wrkcalcs.unitid) AND (escunits.unitid = wrkenrolments.unitid) AND (wrkperiods.periodid = wrkenrolments.periodid) AND (wrkcalcs.locationid = 1) AND (wrkcalcs.periodid IN (4, 6)) GROUP BY wrkperiods.periodname, escunits.unitname, wrklocations.LOCATION, wrkenrolments.eftsu Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208032 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
