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]
