Jump to content

Best way to optimise these SQL statements


chelnov63

Recommended Posts

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

 

 

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)

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.