21 replies to this topic

[monkeymoo1]

Hi

Our site receives about 7000 uniques a day. With about 50,000 page views.
15 Categories + 30 ish Subcategories
3500 Products.
Our server load tends to hover around 3 or 4 - (Linux Debian 1gb Memory etc)

Looking at the Mysql logs I noticed that all the SQL seemed to be doing was counting categories even with the Mysql Cache on

Basically everytime the categories menu loaded it was counting the directorys from the DB. Thousands and thousands of times a day!!!

I removed the category count function from /catalog/includes/boxes/categories.php and removed the corresponding function in catalog/includes/functions/general.php

this bit:-

////
// Return true if the category has subcategories
// TABLES: categories
  function tep_has_category_subcategories($category_id) {
	$child_category_query = tep_db_query("select count(*) as count from " . TABLE_CATEGORIES . " where parent_id = '" . $category_id . "'");
	$child_category = tep_db_fetch_array($child_category_query);

	if ($child_category['count'] > 0) {
	  return true;
	} else {
	  return false;
	}
  }

////

The server has dropped down to a load average of 1 ish and the site absolutely zooooms now :-)

Cheers

Edited by monkeymoo1, 08 May 2006, 21:44.

[Jan Zonjee]

monkeymoo1, on May 8 2006, 11:42 PM, said:

Basically everytime the categories menu loaded it was counting the directorys from the DB. Thousands and thousands of times a day!!!

I assume this contribution could handle that problem. Actually, there is a whole thread devoted to this subject with a lot more suggestions to speed up your site.

[swtnhpy]

Used the contribution mentioned above...got nothing but errors with install.

[loosefigures]

Are you sure there are no negative effects?

[swtnhpy]

Yes, there are negative effects...bunches of errors.

[Jan Zonjee]

swtnhpy, on May 13 2006, 01:17 AM, said:

Yes, there are negative effects...bunches of errors.

I wonder if that is due to the contribution or your skills in adding them...

[insomniac2]

It may also not work correctly if you are not using a stock osc version of includes/boxes/categories.php.

There are a lot of modified categories.php files out there.

[swtnhpy]

JanZ, on May 13 2006, 10:26 AM, said:

I wonder if that is due to the contribution or your skills in adding them...

HMMM...that was pretty rude IMO. My skills in adding them are just fine seeing as how I have over 20 contribs added to my sites.

[mikey boy]

I'm confused. I would like to remove the Category Count overhead, but I can only find the code snippet shown below in catalog/includes/functions/general.php. I've searched catalog/includes/boxes/categories.php (unmodified) but I don't see anything like the code snppet there.

Is it different in catalog/includes/boxes/categories.php & if so what is it?

I've come across mention of removing Category Count quite oten, but this is the closest 've seen on actually how to do it.

Thanks.

Mike

monkeymoo1, on May 8 2006, 09:42 PM, said:

Our site receives about 7000 uniques a day. With about 50,000 page views.
15 Categories + 30 ish Subcategories
3500 Products.
Our server load tends to hover around 3 or 4 - (Linux Debian 1gb Memory etc)

Looking at the Mysql logs I noticed that all the SQL seemed to be doing was counting categories even with the Mysql Cache on

Basically everytime the categories menu loaded it was counting the directorys from the DB. Thousands and thousands of times a day!!!

I removed the category count function from /catalog/includes/boxes/categories.php and removed the corresponding function in catalog/includes/functions/general.php

this bit:-

////
// Return true if the category has subcategories
// TABLES: categories
  function tep_has_category_subcategories($category_id) {
	$child_category_query = tep_db_query("select count(*) as count from " . TABLE_CATEGORIES . " where parent_id = '" . $category_id . "'");
	$child_category = tep_db_fetch_array($child_category_query);

	if ($child_category['count'] > 0) {
	  return true;
	} else {
	  return false;
	}
  }

////

The server has dropped down to a load average of 1 ish and the site absolutely zooooms now :-)

Cheers

[eww]

isn't there a stock function in the admin panel that allows you to turn it off with the click of a button?
i don't remember having to remove any code to do this...
Admin -> My Store -> Show Category Counts -> false

[Jamez]

eww, on Sep 12 2006, 05:36 AM, said:

isn't there a stock function in the admin panel that allows you to turn it off with the click of a button?
i don't remember having to remove any code to do this...
Admin -> My Store -> Show Category Counts -> false

It turns it off but the queries for it still run if I remember rightly.

[boxtel]

Jamez, on Sep 13 2006, 01:06 AM, said:

It turns it off but the queries for it still run if I remember rightly.

nope

[kyotee]

installed the contrib that JanZ mentioned and noticed a huge increase in speed.....thanks

[sleepy_hero]

THANKS MAN, IT WORKED WELL ENOUGH FOR ALL MY OSC SITES.

monkeymoo1, on May 8 2006, 01:42 PM, said:

Hi

Our site receives about 7000 uniques a day. With about 50,000 page views.
15 Categories + 30 ish Subcategories
3500 Products.
Our server load tends to hover around 3 or 4 - (Linux Debian 1gb Memory etc)

Looking at the Mysql logs I noticed that all the SQL seemed to be doing was counting categories even with the Mysql Cache on

Basically everytime the categories menu loaded it was counting the directorys from the DB. Thousands and thousands of times a day!!!

I removed the category count function from /catalog/includes/boxes/categories.php and removed the corresponding function in catalog/includes/functions/general.php

this bit:-

////
// Return true if the category has subcategories
// TABLES: categories
  function tep_has_category_subcategories($category_id) {
	$child_category_query = tep_db_query("select count(*) as count from " . TABLE_CATEGORIES . " where parent_id = '" . $category_id . "'");
	$child_category = tep_db_fetch_array($child_category_query);

	if ($child_category['count'] > 0) {
	  return true;
	} else {
	  return false;
	}
  }

////

The server has dropped down to a load average of 1 ish and the site absolutely zooooms now :-)

Cheers

[zalik22]

monkeymoo1, on May 8 2006, 04:42 PM, said:

Hi

Our site receives about 7000 uniques a day. With about 50,000 page views.
15 Categories + 30 ish Subcategories
3500 Products.
Our server load tends to hover around 3 or 4 - (Linux Debian 1gb Memory etc)

Looking at the Mysql logs I noticed that all the SQL seemed to be doing was counting categories even with the Mysql Cache on

Basically everytime the categories menu loaded it was counting the directorys from the DB. Thousands and thousands of times a day!!!

I removed the category count function from /catalog/includes/boxes/categories.php and removed the corresponding function in catalog/includes/functions/general.php

this bit:-

////
// Return true if the category has subcategories
// TABLES: categories
  function tep_has_category_subcategories($category_id) {
	$child_category_query = tep_db_query("select count(*) as count from " . TABLE_CATEGORIES . " where parent_id = '" . $category_id . "'");
	$child_category = tep_db_fetch_array($child_category_query);

	if ($child_category['count'] > 0) {
	  return true;
	} else {
	  return false;
	}
  }

////

The server has dropped down to a load average of 1 ish and the site absolutely zooooms now :-)

Cheers

I removed the code above, what do you mean exactly by "I removed the category count function from /catalog/includes/boxes/categories.php." Can you tell me what code to comment out/delete? I am not sure what to delete.

Thanks!

[nutbolt]

This works in reducing a query in OSC 2.3.1 as well

in Catalog\includes\modules\boxes\bm_categories.php around line 61 comment out (using //) the following:

 // if (tep_has_category_subcategories($counter)) {
     //   $categories_string .= '->';
     // }

in Catalog\includes\functions\general.php around line 421 comment out the following:

////
// Return true if the category has subcategories
// TABLES: categories
//  function tep_has_category_subcategories($category_id) {
//    $child_category_query = tep_db_query("select count(*) as count from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$category_id . "'");
//    $child_category = tep_db_fetch_array($child_category_query);
//
//    if ($child_category['count'] > 0) {
//      return true;
 //   } else {
 //     return false;
 //   }
//  }

IMHO a waste of a query just to show a silly ..> after the subcategory and it slows your site down especially if you have loads of subcategories, the query has to array all of them first.

[nutbolt]

nutbolt, on 23 May 2011, 12:01, said:

This works in reducing a query in OSC 2.3.1 as well

in Catalog\includes\modules\boxes\bm_categories.php around line 61 comment out (using //) the following:

 // if (tep_has_category_subcategories($counter)) {
     //   $categories_string .= '->';
     // }

in Catalog\includes\functions\general.php around line 421 comment out the following:

////
// Return true if the category has subcategories
// TABLES: categories
//  function tep_has_category_subcategories($category_id) {
//    $child_category_query = tep_db_query("select count(*) as count from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$category_id . "'");
//    $child_category = tep_db_fetch_array($child_category_query);
//
//    if ($child_category['count'] > 0) {
//      return true;
 //   } else {
 //     return false;
 //   }
//  }

IMHO a waste of a query just to show a silly ..> after the subcategory and it slows your site down especially if you have loads of subcategories, the query has to array all of them first.

Ignore the above and delete the // as it seems to fail when you go in deeper to the sub cats.. oh well

[fbas]

a reasonable workaround might be to simply cache the category counts once daily (or when admin alters categories) so you get the benefit of that info without the overhead.

[panicgripdesigns]

The most recent poster is not commenting out product counts, which would be helpful. He/she is commenting out a query to test for SUBCATEGORIES. Bad idea...

(Not trying to sound rude. Sorry its late and I'm tyoing on my cell)

Edited by panicgripdesigns, 14 July 2011, 07:24.

[Rabaeys]

I have used some of the code from the contribution and they gave a huge boost in performance. I have not used all of them because I got some errors.
But I think this comes because I the contribution is written for osc 2.2 and not for 2.3