monkeymoo1 Posted May 8, 2006 Share Posted 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 Link to comment Share on other sites More sharing options...
Jan Zonjee Posted May 8, 2006 Share Posted 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. Link to comment Share on other sites More sharing options...
swtnhpy Posted May 12, 2006 Share Posted May 12, 2006 Used the contribution mentioned above...got nothing but errors with install. Link to comment Share on other sites More sharing options...
loosefigures Posted May 12, 2006 Share Posted May 12, 2006 Are you sure there are no negative effects? Link to comment Share on other sites More sharing options...
swtnhpy Posted May 12, 2006 Share Posted May 12, 2006 Yes, there are negative effects...bunches of errors. Link to comment Share on other sites More sharing options...
Jan Zonjee Posted May 13, 2006 Share Posted 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: Link to comment Share on other sites More sharing options...
insomniac2 Posted May 13, 2006 Share Posted 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. Link to comment Share on other sites More sharing options...
swtnhpy Posted May 15, 2006 Share Posted 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. Link to comment Share on other sites More sharing options...
mikey boy Posted September 9, 2006 Share Posted 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 Link to comment Share on other sites More sharing options...
Guest Posted September 12, 2006 Share Posted 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 Link to comment Share on other sites More sharing options...
Jamez Posted September 12, 2006 Share Posted 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: Link to comment Share on other sites More sharing options...
boxtel Posted September 12, 2006 Share Posted September 12, 2006 It turns it off but the queries for it still run if I remember rightly. :rolleyes: nope Treasurer MFC Link to comment Share on other sites More sharing options...
kyotee Posted December 21, 2006 Share Posted December 21, 2006 installed the contrib that JanZ mentioned and noticed a huge increase in speed.....thanks Link to comment Share on other sites More sharing options...
sleepy_hero Posted January 31, 2007 Share Posted 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 Link to comment Share on other sites More sharing options...
zalik22 Posted February 4, 2007 Share Posted 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! Link to comment Share on other sites More sharing options...
nutbolt Posted May 23, 2011 Share Posted 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. Link to comment Share on other sites More sharing options...
nutbolt Posted May 23, 2011 Share Posted 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 Link to comment Share on other sites More sharing options...
fbas Posted June 7, 2011 Share Posted 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. Link to comment Share on other sites More sharing options...
panicgripdesigns Posted July 14, 2011 Share Posted 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) A little knowledge of php goes a long way. Link to comment Share on other sites More sharing options...
Rabaeys Posted September 3, 2011 Share Posted 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 Link to comment Share on other sites More sharing options...
acidvertigo Posted September 3, 2011 Share Posted September 3, 2011 You can follow this thread about speed optimization: http://www.oscommerce.com/forums/topic/119077-a-store-speed-optimization-in-progress/ Link to comment Share on other sites More sharing options...
RMD27 Posted September 29, 2011 Share Posted 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? Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.