Best way to optimise these SQL statements

[chelnov63]

Hi guys is there someway i can optimise/streamline this code...it seems a bit cumbersome, do i really have to write so many queries? There are quite a few more sectors..wondering if somehow i can get the results using only one sql statement:

 

example:

 

$result_contacts = mysql_query("SELECT * FROM company WHERE sector='Retail'");
echo "The number of companies in retail is: ".mysql_num_rows($result_contacts);

$result_contacts = mysql_query("SELECT * FROM company WHERE sector='Finance'");
echo "The number of companies in finance is: ".mysql_num_rows($result_contacts);

$result_contacts = mysql_query("SELECT * FROM company WHERE sector='Leisure'");
echo "The number of companies in Leisure is: ".mysql_num_rows($result_contacts);

 

Thanks in advance

 

[chelnov63]

not sure why this has been moved from the php section.. maybe i titled it wrong? but this isnt really a mySQL question..

[Mchl]

Well.. it is actually

SELECT sector, COUNT(*)  AS companyCount FROM company GROUP BY sector

[Maq]

not sure why this has been moved from the php section.. maybe i titled it wrong? but this isnt really a mySQL question..

 

I don't see how this could possibly be a PHP question...?

 

 

EDIT: Mchl, beat me to it.

[chelnov63]

its not?  ... thanks for your help..just what i was looking for... Cheers

[kickstart]

Hi

 

Basic improvement:-

 

$result_contacts = mysql_query("SELECT count(*) AS RetailCount FROM company WHERE sector='Retail'");
if ($row = mysql_fetch_array($result_contacts))
echo "The number of companies in retail is: ".$row['RetailCount'];

$result_contacts = mysql_query("SELECT count(*) AS FinanceCount FROM company WHERE sector='Finance'");
if ($row = mysql_fetch_array($result_contacts))
echo "The number of companies in finance is: ".$row['FinanceCount'];

$result_contacts = mysql_query("SELECT count(*) AS LeisureCount FROM company WHERE sector='Leisure'");
if ($row = mysql_fetch_array($result_contacts))
echo "The number of companies in leisure is: ".$row['LeisureCount'];

 

but that is still 3 seperate calls. You could try:-

 

$result_contacts = mysql_query("SELECT (SELECT count(*)  FROM company WHERE sector='Retail') AS RetailCount, (SELECT count(*) FROM company WHERE sector='Finance') AS FinanceCount,(SELECT count(*) FROM company WHERE sector='Leisure') AS LeisureCount  ");
if ($row = mysql_fetch_array($result_contacts))
{
echo "The number of companies in retail is: ".$row['RetailCount'];
echo "The number of companies in finance is: ".$row['FinanceCount'];
echo "The number of companies in leisure is: ".$row['LeisureCount'];
}

 

All the best

 

Keith

[chelnov63]

thanks kickstart.. but Mchl's works perfectly and suits my needs as i have about 20 different sectors.... yours and mine would get pretty lengthy

[Mchl]

Not to mention pretty slow on larger datasets...

[Maq]

thanks kickstart.. but Mchl's works perfectly and suits my needs as i have about 20 different sectors.... yours and mine would get pretty lengthy

 

It's ideal to use GROUP BY, as you can see it will scale if you add more sectors.

[taquitosensei]

This would make it a bit better

$result_contacts = mysql_query("SELECT (SELECT sector,count(*) as Count FROM company  group by sector");
if ($row = mysql_fetch_array($result_contacts))
{
echo "The number of companies in ".$row['sector']." is: ".$row['Count'];

}

[kickstart]

Hi

 

I would agree with the group by if you want more than a nominal number of counts (and with 20 and possibly growing you do), but if you are only looking at a fixed couple of groups and want to avoid a large result set and looping through them (and getting them in a specific odd order), then the single statement would do it well.

 

All the best

 

Keith

[Daniel0]

I would agree with the group by if you want more than a nominal number of counts (and with 20 and possibly growing you do), but if you are only looking at a fixed couple of groups and want to avoid a large result set and looping through them (and getting them in a specific odd order), then the single statement would do it well.

 

Look up the word "scalability". It's mentioned a few times in this topic already. Yours will also be slower. You're running four queries instead of one.

[chelnov63]

So basically SELECT sector, COUNT(*) AS companyCount FROM companies GROUP BY sector works great.. I have another quick question regarding joins

 

at the moment I get the result set looking something like:

 

Sector companyCount

Retail 10

Finance 20

Leisure 12

 

Which is great... now I have another table called contacts(which contains all the contacts i.e employees of these companies) and this ties to the companies table using the company column (which contains the company names) ...

 

I was hoping to get the number of employees in that particular Sector too so the above would look like:

 

Sector companyCount contactsCount

Retail 10 23

Finance 20 32

Leisure 12 11

 

 

How would i go about doing the Join statment..thanks for all your help appreciate it..

 

So for example in this query:

 

SELECT sector, COUNT(*) AS companyCount FROM companies JOIN contacts ON(companies.company = contact.company) GROUP BY sector

 

where/how would i put the count(*) for the contacts database in the above query to get the column contactsCount

 

 

 

 

[kickstart]

Hi

 

I understand scaleability. The count(*) and group by would be doing multiple counts, which could (from his initial post) be completely wasted (ie, if there were multiple groups which were not required). Sure you could use an in statement to reduce the number to those required but not order them without an equally unscalable custom order by clause, or by joining to another table containing the order clause for the groups.

 

My suggestion was for a single query with some sub queries, resulting in a single row being returned and no need to loop round returning seperate rows. Should be no major difference in SQL load (possibly lower), but a saving in php processing. But as said pointless if you want more than a fixed nominal number of groups (but from his initial post it appeared that what he wanted was just a fixed tiny number of groups).

 

All the best

 

Keith

[Mchl]

Turns out to be a MySQL question after all?

 

That would look something along these lines

 

SELECT
  sector,
  COUNT(cm.company) AS companyCount,
  COUNT(ct.contact) AS contactCount 
FROM companies AS cm
LEFT JOIN
contacts AS ct
USING company

[chelnov63]

lol yeah looks like a mysql question more n more

 

i tried:

 

SELECT sector, COUNT(cm.company) AS companyCount, COUNT(ct.contact) AS contactCount FROM companies AS cm LEFT JOIN contacts AS ct USING company

 

but i get the error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'company' at line 1

 

The tables are called contacts and companies and the pk/fk between them is company column... am i missing something?

 

Thanks for you help..if the above works.. it will be awesome..uve saved me hours!!

 

what does ct.contact in COUNT(ct.contact) refer to?  I take it ct is the contacts table and contact is refering to a column in the contacts table? i dont have a contact column in the contact, i have id, first_name, surname, column etc etc..

 

[Mchl]

Put () around company

USING (company)

 

And yeah, ct.contact can be changed to ct.id (or other column form contacts) in your case

[kickstart]

Hi

 

Think a "GROUP BY sector" is also required.

 

I played around and did it another way by prefer Mchls suggestion.

 

All the best

 

Keith

[Mchl]

Sure. GROUP BY will certainly help here

[chelnov63]

Yep ur right kickstart cheers, just added that in.. 

 

thanks mate i think we are getting there:

 

I added the GROUP by sector back in at the end:

 

SELECT sector, COUNT(cm.company) AS companyCount, COUNT(ct.id) AS contactCount FROM companies AS cm LEFT JOIN contacts AS ct USING (company) GROUP by sector;

 

Im not getting an error but im getting both columns saying the same thing e.g

 

Sector                companyCount              contactsCount

Retail                          10                          10

Finance                        20                          20

Leisure                        12                          12

 

 

[Mchl]

Obviously... that wouldn't work... Silly me ;P  How about

 

SELECT 
  cm.sector, 
  COUNT(cm.company) AS companyCount, 
  ct.contactCount 
FROM 
  companies AS cm 
LEFT JOIN 
(SELECT company, COUNT(*) AS contactCount FROM contacts GROUP BY company) AS ct
USING (company)
GROUP by sector;

[kickstart]

Hi

 

Give this a try. This should give the same results (just to ensure that the problem is the SQL rather than just luck with the data).

 

SELECT sector, COUNT( * ) AS CompCount, (

SELECT COUNT( * ) 
FROM contacts z
JOIN companies y ON z.company = y.company
WHERE a.sector = y.sector
)
FROM companies a
GROUP BY sector

 

All the best

 

Keith

[chelnov63]

awesome thanks guys... kickstart yours seem to give the correct results, thanks so much for your help ... mcl thanks for all your help as well mate really appreciate it.. your results seem to give different results for some reason not sure why ...

 

i have to run to a meeting now..but i'll thoroughly test both solutions again and post back..

 

thanks again..

[Mchl]

Probably needs some more tweaking and it will give correct results as well. It should also be faster than subquery in column list.

[kickstart]

Probably needs some more tweaking and it will give correct results as well. It should also be faster than subquery in column list.

 

I can't see why your shouldn't work and it does appear more elegant.

 

Playing with it, it seems to be giving the count of the number of contacts for the first matching company. As though it is doing the grouping prior to the join.

 

All the best

 

Keith