Help! Is there a better way to write this?

MrSteve

Hello,

On one of my websites - http://www.staff.ie - you will see there is a section called "Jobs by Category".

I want to display the number of jobs per category next to each category name.

So instead of having -

IT / Programming

it would say -

IT / Programming (50)

The problem is, when I try to implement this it dramatically slows down the loading of the page (from about 1 second to about 10 seconds.)

This is the code which displays the categories and number of jobs per categories -


// while $a2 is each category
while($a2 = $db->sql_fetchrow())
{
      // get the count of active jobs in this specific category
      $q2 = "select count(*) as cat_total from job_offers, job_offers_categories where job_offers.job_status = 'active' and job_offers.job_id = job_offers_categories.job_id and job_offers_categories.job_category_id = '$a2[category_id]' ";
      $db1->sql_query($q2);
      $a3 = $db1->sql_fetchrow();

  $categories .= "<td width=\"33%\"><a href=\"category/$a2[category_id].html\" class=\"vm_link2\">$a2[category_name] ($a3[cat_total])</a></td>";
}

At the moment I have commented out these bits -


  // get the count of active jobs in this specific category
  $q2 = "select count(*) as cat_total from job_offers, job_offers_categories where job_offers.job_status = 'active' and job_offers.job_id = job_offers_categories.job_id and job_offers_categories.job_category_id = '$a2[category_id]' ";
  $db1->sql_query($q2);
  $a3 = $db1->sql_fetchrow();

Can any of you think of a better way to count the number of jobs per category without dramitically slowing down the page load time?

Any help greatly appreciated.

Thank you!

MrSteve

I should prob add the following info -

A job can have many categories

There is a job table (all the job info) - job_offers
There is a category table (category name and category id) - not used in the query
There is a job category table (job id and category id) - job_offers_categories

MrSteve

If anyone's interested, I've implemented a "dumb" workaround -

A cron is run every 15 minutes which updates the categories table with the total jobs for each category.

Dumb but effective...

Sxooter

Actually, that's pretty smart.

In the mean time, try grouping on job_offers_categories.job_category_id instead of walking through each one.

You might need to look into indexes that could speed things up. For instance are the fields you are joining the two tables on both indexed? Is the field you're

Roger_Ramjet

Basic group by query should do this quite fast, guessing at your table structures a bit

$q2 = "select c.category, count(o.job_category_id) as cat_total 
from job_offers_categories c LEFT JOIN job_offers o ON(c.job_category_id=o.job_category_id)
where o.job_status = 'active' 
GROUP BY c.category, o.job_category_id";

That should list every category in the cats table and the count of rows in the jobs table.

Don't really understand your table layout so mad up some of it but you should get the idea.

xblue

You may need to add "OR ISNULL(o.job_status)" to the where clause in order to get empty categories returned, I think.

Piranha

No, not with a LEFT JOIN xblue. I think that you might want to take a look at the join syntax.

xblue

Yes, exactly with a left join.

Take a look at the manual you linked to:
"If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table".

What does this mean here? No jobs: all columns from the job table will be NULL including the status flag. Requiring it to be 'active' will result in that these records with 0 jobs are not returned, thus nullifying the benefit of using a left join in the first place.

Roger_Ramjet

No, you misunderstand the way this works. The where clause limits the rows in the right table that will be joined to the left table. It will always return all the rows from the left table.