Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Massively Speed up Site and Reduce Server Load


monkeymoo1

Recommended Posts

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 3 months later...

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

  • 3 months later...
  • 1 month later...

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

Link to comment
Share on other 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

 

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!

Link to comment
Share on other sites

  • 4 years later...

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

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.

Link to comment
Share on other sites

  • 1 month later...

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)

A little knowledge of php goes a long way.

Link to comment
Share on other sites

  • 1 month later...

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

Link to comment
Share on other sites

  • 4 weeks later...

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?

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...