Massively Speed up Site and Reduce Server Load

monkeymoo1 · May 8, 2006

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

Jan Zonjee · May 8, 2006

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 · May 12, 2006

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

loosefigures · May 12, 2006

Are you sure there are no negative effects?

swtnhpy · May 12, 2006

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

Jan Zonjee · May 13, 2006

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

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

insomniac2 · May 13, 2006

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 · May 15, 2006

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

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 · September 9, 2006

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

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

September 12, 2006

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 · September 12, 2006

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. :rolleyes:

boxtel · September 12, 2006

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

nope

kyotee · December 21, 2006

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

sleepy_hero · January 31, 2007

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

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 · February 4, 2007

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 · May 23, 2011

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 · May 23, 2011

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 · June 7, 2011

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 · July 14, 2011

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)

Rabaeys · September 3, 2011

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

acidvertigo · September 3, 2011

You can follow this thread about speed optimization:

http://www.oscommerce.com/forums/topic/119077-a-store-speed-optimization-in-progress/

RMD27 · September 29, 2011

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.

can this be done on the admin side of a 2.2 install as well?

Massively Speed up Site and Reduce Server Load

Recommended Posts

monkeymoo1

Jan Zonjee

swtnhpy

loosefigures

swtnhpy

Jan Zonjee

insomniac2

swtnhpy

mikey boy

Guest

Jamez

boxtel

kyotee

sleepy_hero

zalik22

nutbolt

nutbolt

fbas

panicgripdesigns

Rabaeys

acidvertigo

RMD27

Archived

Browse

Activity