Mchl Posted April 16, 2009 Share Posted April 16, 2009 [slaps head] Of course it is XD The 'company' column is aggregated in companies table... I guess I'm too sleepy today to write useful queries I'll have to think about a bit more... Link to comment https://forums.phpfreaks.com/topic/154358-best-way-to-optimise-these-sql-statements/page/2/#findComment-811682 Share on other sites More sharing options...
fenway Posted April 16, 2009 Share Posted April 16, 2009 This thread far too long for me to catch up... where do things stand now? Link to comment https://forums.phpfreaks.com/topic/154358-best-way-to-optimise-these-sql-statements/page/2/#findComment-811761 Share on other sites More sharing options...
Mchl Posted April 16, 2009 Share Posted April 16, 2009 The OP has working query, but I do not... but I'm working on it:D SELECT cm.sector, COUNT(cm.company) AS companyCount, ct.contactCount FROM companies AS cm LEFT JOIN ( SELECT cm.sector, COUNT(ct.ID) AS contactCount FROM companies AS cm CROSS JOIN contacts AS ct USING (company) GROUP BY sector) AS ct USING (sector) GROUP BY sector Link to comment https://forums.phpfreaks.com/topic/154358-best-way-to-optimise-these-sql-statements/page/2/#findComment-811775 Share on other sites More sharing options...
kickstart Posted April 16, 2009 Share Posted April 16, 2009 Hi That looks to me to work when I tried it. Not sure on the efficiency of having the extra join. All the best Keith Link to comment https://forums.phpfreaks.com/topic/154358-best-way-to-optimise-these-sql-statements/page/2/#findComment-811822 Share on other sites More sharing options...
Mchl Posted April 16, 2009 Share Posted April 16, 2009 Neither do I. Some benchmarking would be needed. Takes 0.3 seconds on my computer where 'contacts' has 180000+ rows and 'companies' has 3200+ rows. (these are actually different tables from project I am working on, but they're similarly related) Link to comment https://forums.phpfreaks.com/topic/154358-best-way-to-optimise-these-sql-statements/page/2/#findComment-811825 Share on other sites More sharing options...
kickstart Posted April 16, 2009 Share Posted April 16, 2009 Hi I hope the OP hasn't got tables that size. It he has and is paging the info then I think some optimising might be required (maybe even to split out the counts and just select those which are to be displayed on screen). All the best Keith Link to comment https://forums.phpfreaks.com/topic/154358-best-way-to-optimise-these-sql-statements/page/2/#findComment-811853 Share on other sites More sharing options...
Mchl Posted April 16, 2009 Share Posted April 16, 2009 Somehow I doubt that dividing companies into several hundred 'sectors' would be practical Link to comment https://forums.phpfreaks.com/topic/154358-best-way-to-optimise-these-sql-statements/page/2/#findComment-811858 Share on other sites More sharing options...
kickstart Posted April 16, 2009 Share Posted April 16, 2009 Hi You never know . Users are funny things. All the best Keith Link to comment https://forums.phpfreaks.com/topic/154358-best-way-to-optimise-these-sql-statements/page/2/#findComment-811864 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.