I think you are doing the best you can with mySQL.... considering you have no SP's, views etc... :-)
-----Original Message----- From: Dave Phipps [mailto:[EMAIL PROTECTED]] Sent: 16 January 2003 13:09 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Confusing SQL The database is actually MySQL and whilst I could and should cache the circuits query this is only hit once as I call the other 2 queries from within the output of the circuits query and hence cannot cache them as their result is dependent on a var which changes each time the loop is executed. Here's a code snip: <cfoutput query="listCircuits" group="circuitid"> <cfinclude template="qry_list_menus.cfm"> <tr><td class="header" colspan="2"><img src="images/arrow_right.gif"><b>#displayname#</b></td><td width="75" class="list_italic_g">Created</td><td width="75" class="list_italic_g">Modified</td></tr> <cfoutput group="contentid"> <cfparam name="submenuid" default="0"> <cfloop query="list_menus"> <cfinclude template="qry_list_content.cfm"> <tr <cfif list_menus.currentrow MOD 2>bgcolor="##D9D7E6"</cfif>> <td width="162" class="list"><img src="images/1px_spacer.gif" width="12" height="9"> <a href="#request.self#?fuseaction=#XFA.edit#&mode=edit&circuitid=#list_menus.c ircuitid#&menuid=#list_menus.menuid#&contentid=#list_menus.contentid#&#reque st.urltoken#">#list_menus.pagetitle#</a></td> <td width="348" class="list_italic">'#contentdescription#...'</td> <td width="75" class="list">#LSDateFormat(list_menus.contentdate, "dd/mm/yy")#</td> <td width="75" class="list">#LSDateFormat(list_menus.lastupdate, "dd/mm/yy")#</td> </tr> <cfif list_content.recordcount> <tr> <td colspan="4" class="header"><img src="images/1px_spacer.gif" width="12" height="9"><img src="images/arrow_right.gif">#list_menus.menuname#</td> </tr> </cfif> <cfloop query="list_content"> <tr <cfif list_content.currentrow MOD 2>bgcolor="##D9D7E6"</cfif>> <td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"> <a href="#request.self#?fuseaction=#XFA.edit#&mode=edit&circuitid=#list_menus.c ircuitid#&menuid=#list_content.menuid#&submenuid=#list_content.submenuid#&co ntentid=#list_content.contentid#&#request.urltoken#">#list_content.pagetitle #</a></td> <td width="348" class="list_italic">'#contentdescription#...'</td> <td width="75" class="list">#LSDateFormat(list_content.contentdate, "dd/mm/yy")#</td> <td width="75" class="list">#LSDateFormat(list_content.lastupdate, "dd/mm/yy")#</td> </tr> </cfloop> </cfloop> </cfoutput> <tr><td class="header" colspan="4"><br> <br></td></tr> </cfoutput> The result of the above code looks something like this: <tr><td class="header" colspan="2"><img src="images/arrow_right.gif"><b>Academic</b></td><td width="75" class="list_italic_g">Created</td><td width="75" class="list_italic_g">Modified</td></tr> <tr bgcolor="#D9D7E6"><td width="162" class="list"><img src="images/1px_spacer.gif" width="12" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m enuid=14&contentid=22&CFID=129319&CFTOKEN=71536666">Introduction</a></td><td width="348" class="list_italic">'Introduction...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr> <tr ><td width="162" class="list"><img src="images/1px_spacer.gif" width="12" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m enuid=15&contentid=23&CFID=129319&CFTOKEN=71536666">Curriculum</a></td><td width="348" class="list_italic">'The School Organisation...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">14/01/03</td></tr> <tr><td colspan="4" class="header"><img src="images/1px_spacer.gif" width="12" height="9"><img src="images/arrow_right.gif">Curriculum</td></tr> <tr bgcolor="#D9D7E6"><td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m enuid=15&submenuid=5&contentid=25&CFID=129319&CFTOKEN=71536666">Pre-Prep</a> </td><td width="348" class="list_italic">'Pre-Prep Curriculum...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr> <tr ><td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m enuid=15&submenuid=6&contentid=26&CFID=129319&CFTOKEN=71536666">Prep</a></td ><td width="348" class="list_italic">'Prep School Curriculum...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr> <tr bgcolor="#D9D7E6"><td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m enuid=15&submenuid=7&contentid=27&CFID=129319&CFTOKEN=71536666">Pastoral Care</a></td><td width="348" class="list_italic">'Pastoral Care...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">04/12/02</td></tr> <tr ><td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m enuid=15&submenuid=8&contentid=28&CFID=129319&CFTOKEN=71536666">Trips</a></t d><td width="348" class="list_italic">'Trips...'</td><td width="75" class="list">01/12/02</td><td width="75" class="list">08/01/03</td></tr> <tr bgcolor="#D9D7E6"><td width="162" class="list"><img src="images/1px_spacer.gif" width="24" height="9"><a href="index.cfm?fuseaction=cms.wz_content_addcontent&mode=edit&circuitid=4&m enuid=15&submenuid=10&contentid=47&CFID=129319&CFTOKEN=71536666">Music</a></ td><td width="348" class="list_italic">'Music...'</td><td width="75" class="list">10/12/02</td><td width="75" class="list">08/01/03</td></tr> It all works but is a little heavy handed. Is there a better way. Could I build an array or structure to hold the info I need? Cheers Dave At 12:48 1/16/2003 +0000, you wrote: >Oh and the suggestion would be (if the circuits/menus don't change too >much): > >Cache the queries > >This way the database doesn't get hit so much. The content may change too, >but it might be useful to have, for example, a 10 or 30 minute cache on >those too. Just to take the load off. > >Paul > > > Hi, > > > > I have three queries that build a navigation system which is > > running really > > slowly. This is because I haven't been able to combine the > > three queries > > into one and therefore have to do nested loops which results > > in about 50 > > queries running for some sections of the site. > > > > The three queries are below: > > > > ListCircuits: > > > > SELECT circuits.circuitid, circuits.circuitname, circuits.contentid, > > circuits.displayname, content.contentid, content.pagetitle, > > content.contentdate, content.contentdescription, content.lastupdate, > > content.app_status, content.lastupdate, groups.groupid, > > groups.groupname FROM groups, content_group, circuits LEFT > > JOIN content ON > > circuits.contentid = content.contentid > > WHERE content.contentid = content_group.contentid > > AND groups.groupid = content_group.groupid > > ORDER BY circuits.circuitid > > > > ListMenus > > > > SELECT circuits.circuitid, circuits.circuitname, > > circuits.displayname, > > menus.menuid, menus.menuname, menus.contentid, submenus.submenuid, > > content.pagetitle, content.contentdate, content.contentdescription, > > content.lastupdate, content.app_status, groups.groupid, > > groups.groupname FROM groups, circuits LEFT JOIN menus ON > > circuits.circuitid = > > menus.circuitid LEFT JOIN submenus ON menus.menuid = > > submenus.menuid LEFT > > JOIN content ON menus.contentid = content.contentid LEFT JOIN > > content_group ON content.contentid = content_group.contentid > > WHERE content_group.groupid = groups.groupid AND > > circuits.circuitid = #listCircuits.circuitid# AND > > content.pagetitle != 'The Week Ahead' GROUP BY > > content.contentid ORDER BY menus.menuid > > > > ListContent > > > > SELECT submenus.submenuid, submenus.menuid, submenus.submenuname, > > content.contentid, content.pagetitle, content.contentdate, > > content.contentdescription, content.lastupdate, content.app_status, > > content.isDeleted, groups.groupname, groups.groupid > > FROM submenus LEFT JOIN content ON submenus.submenuid = > > content.submenuid > > LEFT JOIN content_group ON content.contentid = > > content_group.contentid LEFT > > JOIN groups ON content_group.groupid = groups.groupid > > WHERE (submenus.menuid = #list_menus.menuid#) > > ORDER BY submenus.submenuid, content.contentid > > > > Can anyone see a way to pool these three queries into one query? The > > problem I have found is that each table (circuits, menus, > > submenus) has a > > contentid (which is the default page for that section). > > > > Any help on this? > > > > Cheers > > > > Dave > > > > > > > > -- > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: > > [EMAIL PROTECTED] For human help, e-mail: > > [EMAIL PROTECTED] > > > > > > > > > > >-- >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >For human help, e-mail: [EMAIL PROTECTED] ============================================ Phipps CF Development Oxford, Oxfordshire. Telephone: +44(0)7718 896696 http://www.phipps-cf.co.uk ============================================ -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
