dfield22 Posted January 17, 2007 Share Posted January 17, 2007 KUDO's KUDO's KUDO's Major Back Pat....My Hats Off to you all CHEMO YOU DA MAN and everyone else that have helped with this - congrats. I wish I had found this topic earlier but I am very happy with the results that I have gotten with little to no effort. Keep up the good work. THE MAN CHEMO - THE MAN .... :thumbsup: ( I wish you were still here !) Diane Link to comment Share on other sites More sharing options...
alfinator Posted February 2, 2007 Share Posted February 2, 2007 Hi Chemo, I would love to hire you if you are available for some freelance work. Please email me at "alfredo -at- nevarez -dot- net" if you're interested, and along with everyone else here, thank you for your continued input and advice. Also, what is the deal with the permanent ban? Why don't the "powers that be" work on improving osCommerce instead of this nonsense? OK people...I don't get back here to the forum that often since I'm perpetually banned but I'll give you the best advice I can in under 15 minutes. First, DO NOT INSTALL PAGE CACHE. That's right...don't freakin' install it. Listen, I coded the contribution and regret it every time someone mentions it to me. I created it as a last resort...not a first line defense against poor performance. To be perfectly honest, it was created for those poor store owners that installed 1,000 contributions and was trying to tame a bloated beast without hope of properly optimizing the code/queries. Install the fewest number of contributions to make your store run and leave it at that. Second, cache elements at the data level and NOT the resulting output. The stock cache functions can be tweaked very minimally to cache everything you need from the category parent queries to the best seller list. In fact, using nothing but stock cache functions (with tweaks) allows me to get most sites down to 5 queries or less with sub .1 second render times. Third, don't get lost in the number of queries but focus on the ones that take the longest to execute. 100 queries that use proper indexes will execute faster and with less system resources than 1 poorly written also purchased SQL statement. Fourth, get rid of all excess insert queries as they flush the query cache. This means specials, featured products, and even the product number of times viewed. If it inserts and you don't need it for mission critical metrics get rid of it. Fifth, use your head and stop looking for shortcuts. Optimize your code and queries BEFORE sacrificing the dynamic nature of your site. Bobby aka "Chemo" Link to comment Share on other sites More sharing options...
TracyS Posted February 14, 2007 Share Posted February 14, 2007 First, if you notice there are redundant queries in there...specifically, this one: select parent_id from categories where categories_id = '487' This is caused by one of two functions in the osC library. The first is tep_get_path() located in the includes/functions/general.php file. Change it to this: function tep_get_path($current_category_id = '') { global $cPath_array; static $cats = array(); if (tep_not_null($current_category_id)) { $cp_size = sizeof($cPath_array); if ($cp_size == 0) { $cPath_new = $current_category_id; } else { $cPath_new = ''; if ( !isset($cats[($cp_size-1)]) ){ $last_category_query = tep_db_query("select parent_id from " . TABLE_CATEGORIES . " where categories_id = '" . (int)$cPath_array[($cp_size-1)] . "'"); $last_category = tep_db_fetch_array($last_category_query); $cats[($cp_size-1)] = $last_category['parent_id']; } else { $last_category['parent_id'] = $cats[($cp_size-1)]; } if ( !isset($cats[(int)$current_category_id]) ){ $current_category_query = tep_db_query("select parent_id from " . TABLE_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'"); $current_category = tep_db_fetch_array($current_category_query); $cats[(int)$current_category_id] = $current_category['parent_id']; } else { $current_category['parent_id'] = (int)$current_category_id; } if ($last_category['parent_id'] == $current_category['parent_id']) { for ($i=0; $i<($cp_size-1); $i++) { $cPath_new .= '_' . $cPath_array[$i]; } } else { for ($i=0; $i<$cp_size; $i++) { $cPath_new .= '_' . $cPath_array[$i]; } } $cPath_new .= '_' . $current_category_id; if (substr($cPath_new, 0, 1) == '_') { $cPath_new = substr($cPath_new, 1); } } } else { $cPath_new = implode('_', $cPath_array); } return 'cPath=' . $cPath_new; } This code creates a local cache with a static variable ($cats). This will eliminate the redundant queries that you are seeing. Change the code and see if that solves your problem. Anyone know where the second place is? I did the above modifications - but I still have [20] => select parent_id from categories where categories_id = '17' [21] => select parent_id from categories where categories_id = '25' [22] => select parent_id from categories where categories_id = '17' [23] => select parent_id from categories where categories_id = '29' [24] => select parent_id from categories where categories_id = '17' [25] => select parent_id from categories where categories_id = '28' [26] => select parent_id from categories where categories_id = '17' etc........ On my category pages :huh: I am using STS v4.2 (as well as many other contribs) but I haven't found anything in there that seems to be causing this. Any thoughts? ~Tracy Link to comment Share on other sites More sharing options...
devilgrins Posted February 24, 2007 Share Posted February 24, 2007 hi to all... this is a wonderful asset this thread!!! going through it all is time consuming.. anyway to speed that up ;) anyways.. i installed the query debug and i get these warnings.. though the contrib appears to work fine accept for the warning which i get on the top.. Warning: session_start() [function.session-start]: Cannot send session cookie - headers already sent by (output started at /home/moderate/public_html/sandbox/store/osc/includes/functions/general.php:1303) in /home/moderate/public_html/sandbox/store/osc/includes/functions/sessions.php on line 67 Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /home/moderate/public_html/sandbox/store/osc/includes/functions/general.php:1303) in /home/moderate/public_html/sandbox/store/osc/includes/functions/sessions.php on line 67 can someone please kindly explain this to me... also.. after reading this all.. is page cache contrib still useful even though chemo states it shouldnt be used???????????? thanks for your time and help guys. Link to comment Share on other sites More sharing options...
Becki Posted March 4, 2007 Share Posted March 4, 2007 In includes/modules/new_products.php find this code: CODE �if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) { � �$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); �} else { � �$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); �} ...change it to this code: CODE �if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) { � �$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_name from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); �} else { � �$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_name from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); �} Next, find this line and comment it out like htis: CODE #$new_products['products_name'] = tep_get_products_name($new_products['products_id']); Tried this but... some products in the 'New Products' box don't show names, and when you update one of those products it is shown twice (one with a name & one without). :blink: I have this problem when trying to implement chemo's join optimisation - has there been an answer to this? I looked over 3/4 of this thread and didn't see one! Thanks Becki Link to comment Share on other sites More sharing options...
Jan Zonjee Posted March 4, 2007 Share Posted March 4, 2007 I have this problem when trying to implement chemo's join optimisation - has there been an answer to this? I looked over 3/4 of this thread and didn't see one! Actually, it is missing and pd.language_id = '" . (int)$languages_id . "' after the p.products_status = '1'. Maybe you need to add global $languages_id; in the top of the file too. Link to comment Share on other sites More sharing options...
Becki Posted March 4, 2007 Share Posted March 4, 2007 Actually, it is missing and pd.language_id = '" . (int)$languages_id . "' after the p.products_status = '1'. Maybe you need to add global $languages_id; in the top of the file too. Thanks for the quick answer :) - I'll give it a try tomorrow and post back the results! thanks Becki Link to comment Share on other sites More sharing options...
Becki Posted March 5, 2007 Share Posted March 5, 2007 Thanks for the quick answer :) - I'll give it a try tomorrow and post back the results! thanks Becki I thought i'd posted back here but it doesn't seem to have worked! apllied the change above to the code a bit futher up and it works - thanks! :) That's 9queries down! Link to comment Share on other sites More sharing options...
Becki Posted March 5, 2007 Share Posted March 5, 2007 Thanks for the quick answer :) - I'll give it a try tomorrow and post back the results! thanks Becki I thought i'd posted back here but it doesn't seem to have worked! apllied the change above to the code a bit futher up and it works - thanks! :) That's 9queries down! Link to comment Share on other sites More sharing options...
Guest Posted March 5, 2007 Share Posted March 5, 2007 I was wondering if anybody could help me out. My site is loading 2-4 sec per page. I've renamed my image folder so that no images appear. I am down to 26 queries. My debug info is printing out to the page. I've done many of the optimizations listed. Yet I am still getting a very slow site load. Also my shopping cart is broken from my optimizations. I believe this has to deal with the cookies not loading right. Thank you in advance. Link to comment Share on other sites More sharing options...
Guest Posted March 6, 2007 Share Posted March 6, 2007 aspengrove-design.com Link to comment Share on other sites More sharing options...
Lucky Beau Posted March 6, 2007 Share Posted March 6, 2007 aspengrove-design.com Have you installed the tax contribution? It reduces the tax queries to one query. I've been trying to optimize my shop for the last 2 days and so far I have cut the queries from 38 (2.1 secs) to 12 (1.172 s). I had one heavy page 66 queries, now it is 18 qs. I have # the 'who's on line' and special.php. It seems that you need the special.php when you add products to the special list and to monitor the expiration time (which I would do manually) but the queries run every time you load the pages. My site is heavy with images and flash, so I'm still trying to optimize it. I can not find ways to speed up the products_info.php without hurting the functions, hope to get suggestions here. The product's and category's tables are called many times, also the product option tables, just wonder if there are more efficient ways to get the same results. I'm also trying make the Page Cache contribution work. It will cut the parse time down to 0.9 s, but all the products would dissapear :blush: N Link to comment Share on other sites More sharing options...
Guest Posted March 7, 2007 Share Posted March 7, 2007 I added the tax contribution and everything seemed to have worked. However what is the special.php you are speaking of? Link to comment Share on other sites More sharing options...
Lucky Beau Posted March 10, 2007 Share Posted March 10, 2007 I added the tax contribution and everything seemed to have worked. However what is the special.php you are speaking of? I'm sorry, I was actually referring to: // auto expire special products require(DIR_WS_FUNCTIONS . 'specials.php'); tep_expire_specials(); these lines are in the application_top.php. I disabled (//) those lines, but actually it would be better if somebody can tweak that function a bit so we don't loose the functionality. Link to comment Share on other sites More sharing options...
Becki Posted March 12, 2007 Share Posted March 12, 2007 Hi all, I've integrated the tax class optimisation and the boxes/categories.php one into my already modifed file (so has about half the impact on the optimisation has on the standard categories.php file - i'll try and get it down more), now i still find all these queries, can anyone tell me where there coming from and if i can get rid of them :) Many Thanks [25] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '0' order by c.sort_order, cd.categories_name [26] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '23' order by c.sort_order, cd.categories_name [27] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '37' order by c.sort_order, cd.categories_name [28] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '38' order by c.sort_order, cd.categories_name [29] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '39' order by c.sort_order, cd.categories_name [30] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '40' order by c.sort_order, cd.categories_name [31] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '24' order by c.sort_order, cd.categories_name [32] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '41' order by c.sort_order, cd.categories_name [33] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '42' order by c.sort_order, cd.categories_name [34] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '25' order by c.sort_order, cd.categories_name [35] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '43' order by c.sort_order, cd.categories_name [36] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '44' order by c.sort_order, cd.categories_name [37] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '46' order by c.sort_order, cd.categories_name [38] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '45' order by c.sort_order, cd.categories_name [39] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '27' order by c.sort_order, cd.categories_name [40] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '52' order by c.sort_order, cd.categories_name [41] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '51' order by c.sort_order, cd.categories_name [42] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '53' order by c.sort_order, cd.categories_name [43] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '54' order by c.sort_order, cd.categories_name [44] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '26' order by c.sort_order, cd.categories_name [45] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '47' order by c.sort_order, cd.categories_name [46] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '48' order by c.sort_order, cd.categories_name [47] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '49' order by c.sort_order, cd.categories_name [48] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '50' order by c.sort_order, cd.categories_name [49] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '36' order by c.sort_order, cd.categories_name [50] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '61' order by c.sort_order, cd.categories_name [51] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '55' order by c.sort_order, cd.categories_name [52] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '56' order by c.sort_order, cd.categories_name [53] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '57' order by c.sort_order, cd.categories_name [54] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '58' order by c.sort_order, cd.categories_name [55] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '59' order by c.sort_order, cd.categories_name [56] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '60' order by c.sort_order, cd.categories_name [57] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '30' order by c.sort_order, cd.categories_name [58] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '70' order by c.sort_order, cd.categories_name [59] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '71' order by c.sort_order, cd.categories_name [60] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '28' order by c.sort_order, cd.categories_name [61] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '67' order by c.sort_order, cd.categories_name [62] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '29' order by c.sort_order, cd.categories_name [63] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '34' order by c.sort_order, cd.categories_name [64] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '31' order by c.sort_order, cd.categories_name [65] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '62' order by c.sort_order, cd.categories_name [66] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '63' order by c.sort_order, cd.categories_name [67] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '32' order by c.sort_order, cd.categories_name [68] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '33' order by c.sort_order, cd.categories_name [69] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '64' order by c.sort_order, cd.categories_name [70] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '35' order by c.sort_order, cd.categories_name [71] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '65' order by c.sort_order, cd.categories_name [72] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '68' order by c.sort_order, cd.categories_name [73] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '69' order by c.sort_order, cd.categories_name [74] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '3' order by c.sort_order, cd.categories_name [75] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '13' order by c.sort_order, cd.categories_name [76] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '12' order by c.sort_order, cd.categories_name [77] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '15' order by c.sort_order, cd.categories_name [78] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '11' order by c.sort_order, cd.categories_name [79] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '14' order by c.sort_order, cd.categories_name [80] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '66' order by c.sort_order, cd.categories_name [81] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id = '1' and c.parent_id = '21' order by c.sort_order, cd.categories_name Link to comment Share on other sites More sharing options...
TracyS Posted March 13, 2007 Share Posted March 13, 2007 Hi all, I've integrated the tax class optimisation and the boxes/categories.php one into my already modifed file (so has about half the impact on the optimisation has on the standard categories.php file - i'll try and get it down more), now i still find all these queries, can anyone tell me where there coming from and if i can get rid of them :) Many Thanks Looks to me like they are all the same query, just happening once for each different category. What I would do is start with the page you got the information off of. Look at the source code and view all of the queries. If the query is not there, then look for all of the "include" and "required" files and see which one it is in. Once you know which file has the query, then you will know the purpose of the query. At that point you can turn it into an object so that it only happens once or twice on a page rather than happening once for each category. I had a similar problem with a hack for the DynaMenu contribution to add the products to the menu as well as the categories. So to see an example of the object to pull that information you can check the add-on I uploaded to the DynaMenu contribution page. I'm not the best with programming yet, and I had quite a bit of help in creating the object - so I don't know that I would be of much help in how to create it - I just know that's what worked for me to get rid of multiple queries in that instance :blush: ~Tracy Link to comment Share on other sites More sharing options...
Becki Posted March 14, 2007 Share Posted March 14, 2007 Looks to me like they are all the same query, just happening once for each different category. What I would do is start with the page you got the information off of. Look at the source code and view all of the queries. If the query is not there, then look for all of the "include" and "required" files and see which one it is in. Once you know which file has the query, then you will know the purpose of the query. At that point you can turn it into an object so that it only happens once or twice on a page rather than happening once for each category. I had a similar problem with a hack for the DynaMenu contribution to add the products to the menu as well as the categories. So to see an example of the object to pull that information you can check the add-on I uploaded to the DynaMenu contribution page. I'm not the best with programming yet, and I had quite a bit of help in creating the object - so I don't know that I would be of much help in how to create it - I just know that's what worked for me to get rid of multiple queries in that instance :blush: Hi, Well i found that if i delete this from sts_user_code then i drop 59queries and I don't think anything has been affected but maybe it has - i need to find out what exactly this is for! $sts->start_capture(); echo "\n<!-- Start Category Menu -->\n"; echo tep_draw_form('goto', FILENAME_DEFAULT, 'get', ''); echo tep_draw_pull_down_menu('cPath', tep_get_category_tree(), $current_category_id, 'onChange="this.form.submit();"'); echo "</form>\n"; echo "<!-- End Category Menu -->\n"; $sts->stop_capture('catmenu'); I think I should be down to 20 queries on the index page - not too bad i hope?! (thats after being cached by the standard osc cache - 50ish before) Becki Link to comment Share on other sites More sharing options...
Becki Posted March 14, 2007 Share Posted March 14, 2007 Hi, Well i found that if i delete this from sts_user_code then i drop 59queries and I don't think anything has been affected but maybe it has - i need to find out what exactly this is for! $sts->start_capture(); echo "\n<!-- Start Category Menu -->\n"; echo tep_draw_form('goto', FILENAME_DEFAULT, 'get', ''); echo tep_draw_pull_down_menu('cPath', tep_get_category_tree(), $current_category_id, 'onChange="this.form.submit();"'); echo "</form>\n"; echo "<!-- End Category Menu -->\n"; $sts->stop_capture('catmenu'); I think I should be down to 20 queries on the index page - not too bad i hope?! (thats after being cached by the standard osc cache - 50ish before) Becki Thanks Tracy for posting on the STS forum, so I can delete it and save 59 queries :) Do you know of any other bits of code with STS that generate lots of queries? I'm really only using the stock placeholders/cat menu etc so don't need anything like the code I've just commented out. I've just noticed on my category pages i have lots of: [13] => select count(*) as count from categories where parent_id = '23' [14] => select count(*) as count from categories where parent_id = '24' [15] => select count(*) as count from categories where parent_id = '25' [16] => select count(*) as count from categories where parent_id = '27' [17] => select count(*) as count from categories where parent_id = '26' Any ideas? Thanks becki Link to comment Share on other sites More sharing options...
♥FWR Media Posted March 23, 2007 Share Posted March 23, 2007 Are you still getting lots of .. select parent_id from categories where categories_id = '487' Especially when clicking a category that has multiple children? the problem is that catalog>index.php calls tep_get_path() in catalog>includes>functions>general.php for each link so there are multiple queries. I think I have it solved. Try the below: - In catalog>includes>functions>general.php directly under the tep_get_path function add a new function tep_get_multiple_paths function tep_get_multiple_paths($multiple_category_ids) { global $cPath_array; static $cats = array(); //Mod $categories_parents_result = tep_db_query("SELECT parent_id, categories_id from " . TABLE_CATEGORIES . " where categories_id in (" . $multiple_category_ids . ") "); while ($categories_parents = tep_db_fetch_array($categories_parents_result)) { $category_parent[] = array ( 'categories_id' => $categories_parents['categories_id'], 'parent_id' => $categories_parents['parent_id']); } tep_db_free_result($categories_parents_result); // Housekeeping unset($multiple_category_ids); //Housekeeping $counted = count($category_parent); for($i=0; $i<$counted; $i++) { $current_category_id = $category_parent[$i]['categories_id']; //End Mod if (tep_not_null($current_category_id)) { $cp_size = sizeof($cPath_array); if ($cp_size == 0) { $cPath_new = $current_category_id; } else { $cPath_new = ''; if ( !isset($cats[($cp_size-1)]) ){ $last_category['parent_id'] = (int)$cPath_array[($cp_size-1)]; $cats[($cp_size-1)] = $last_category['parent_id']; } else { $last_category['parent_id'] = $cats[($cp_size-1)]; } if ( !isset($cats[(int)$current_category_id]) ){ $current_category['parent_id'] = $category_parent[$i]['parent_id']; $cats[(int)$current_category_id] = $current_category['parent_id']; } else { $current_category['parent_id'] = (int)$current_category_id; } if ($last_category['parent_id'] == $current_category['parent_id']) { for ($j=0; $j<($cp_size-1); $j++) { $cPath_new .= '_' . $cPath_array[$j]; } } else { for ($j=0; $j<$cp_size; $j++) { $cPath_new .= '_' . $cPath_array[$j]; } } $cPath_new .= '_' . $current_category_id; if (substr($cPath_new, 0, 1) == '_') { $cPath_new = substr($cPath_new, 1); $cPath_list[$category_parent[$i]['categories_id']] = 'cPath=' . $cPath_new; } } } else { $cPath_new = implode('_', $cPath_array); $cPath_list[$category_parent[$i]['categories_id']] = 'cPath=' . $cPath_new; } } unset($counted, $category_parent); return $cPath_list; } Open catalog>index.php find .. $number_of_categories = tep_db_num_rows($categories_query); $rows = 0; while ($categories = tep_db_fetch_array($categories_query)) { $rows++; $cPath_new = tep_get_path($categories['categories_id']); $width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%'; echo ' <td align="center" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">' . tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) . ' ' . $categories['categories_name'] . '</a></td>' . "\n"; if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != $number_of_categories)) { echo ' </tr>' . "\n"; echo ' <tr>' . "\n"; } } // needed for the new products module shown below $new_products_category_id = $current_category_id; Replace with .. $number_of_categories = tep_db_num_rows($categories_query); //New Optimisation Mod to reduce products_name query to just 1 while ($categories = tep_db_fetch_array($categories_query)) { $list_of_categories[] = (int)$categories['categories_id']; $categories_info[] = array ( 'categories_id' => (int)$categories['categories_id'], 'categories_image' => $categories['categories_image'], 'categories_name' => $categories['categories_name'], 'parent_id' => $categories['parent_id']); } tep_db_free_result($categories_query); // Housekeeping if (sizeof($list_of_categories) > 0 ) { $select_list_of_categories = implode(",", $list_of_categories); $multiple_categories = tep_get_multiple_paths($select_list_of_categories); } $rows = 0; $counted = count($categories_info); for($i=0; $i<$counted; $i++) { $rows++; $cPath_new = $multiple_categories[$categories_info[$i]['categories_id']]; $width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%'; echo ' <td align="center" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $multiple_categories[$categories_info[$i]['categories_id']]) . '">' . tep_image(DIR_WS_IMAGES . $categories_info[$i]['categories_image'], $categories_info[$i]['categories_name'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) . ' ' . $categories_info[$i]['categories_name'] . '</a></td>' . "\n"; if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != $number_of_categories)) { echo ' </tr>' . "\n"; echo ' <tr>' . "\n"; } } //End Optimisation Mod // needed for the new products module shown below $new_products_category_id = $current_category_id; Let me know how you get on Ultimate SEO Urls 5 PRO - Multi Language Modern, Powerful SEO Urls KissMT Dynamic SEO Meta & Canonical Header Tags KissER Error Handling and Debugging KissIT Image Thumbnailer Security Pro - Querystring protection against hackers ( a KISS contribution ) If you found my post useful please click the "Like This" button to the right. Please only PM me for paid work. Link to comment Share on other sites More sharing options...
♥FWR Media Posted March 23, 2007 Share Posted March 23, 2007 The result as an example Instead of 11 Select parent_id from categories where parent_id = X queries You should get one like the below select categories_id, parent_id from categories where parent_id in (1,2,3,17,4,8,16,9,6,5,7) Ultimate SEO Urls 5 PRO - Multi Language Modern, Powerful SEO Urls KissMT Dynamic SEO Meta & Canonical Header Tags KissER Error Handling and Debugging KissIT Image Thumbnailer Security Pro - Querystring protection against hackers ( a KISS contribution ) If you found my post useful please click the "Like This" button to the right. Please only PM me for paid work. Link to comment Share on other sites More sharing options...
Becki Posted March 23, 2007 Share Posted March 23, 2007 The result as an example Instead of 11 Select parent_id from categories where parent_id = X queries You should get one like the below select categories_id, parent_id from categories where parent_id in (1,2,3,17,4,8,16,9,6,5,7) Hi, I have about 3times as many [39] => select count(*) as count from categories where parent_id = '77' as I do [52] => select parent_id from categories where categories_id = '23' Will the mods you've made just get rid of the [52] => select parent_id from categories where categories_id = '23' ones? I shall give it a go tonight and thank you very very much if it works :))) Becki Link to comment Share on other sites More sharing options...
TracyS Posted March 23, 2007 Share Posted March 23, 2007 Hi, I have about 3times as many as I do Will the mods you've made just get rid of the ones? I shall give it a go tonight and thank you very very much if it works :))) Becki I believe that the select count queries were covered in this thread or the other thread Chemo started for store optimization. I'll do some searching and see if I can't find the actual post on it - but I'm pretty sure I read something on how to fix that in this thread :thumbsup: ~Tracy Link to comment Share on other sites More sharing options...
TracyS Posted March 23, 2007 Share Posted March 23, 2007 Are you still getting lots of .. select parent_id from categories where categories_id = '487' Especially when clicking a category that has multiple children? the problem is that catalog>index.php calls tep_get_path() in catalog>includes>functions>general.php for each link so there are multiple queries. I think I have it solved. Try the below: - Thank you very much for posting this! I will be giving it a try as well :thumbsup: ~Tracy Link to comment Share on other sites More sharing options...
♥FWR Media Posted March 23, 2007 Share Posted March 23, 2007 Thank you very much for posting this! I will be giving it a try as well :thumbsup: My pleasure. There was an error in tep_get_multiple_paths(). Please use the below: - function tep_get_multiple_paths($multiple_category_ids) { global $cPath_array; static $cats = array(); $cp_size = sizeof($cPath_array); $multiple_category_ids .= "," . $cPath_array[($cp_size-1)]; //Mod $categories_parents_result = tep_db_query("SELECT parent_id, categories_id from " . TABLE_CATEGORIES . " where categories_id in (" . $multiple_category_ids . ") "); while ($categories_parents = tep_db_fetch_array($categories_parents_result)) { $category_parent[] = array ( 'categories_id' => $categories_parents['categories_id'], 'parent_id' => $categories_parents['parent_id']); $parent_by_category[$categories_parents['categories_id']] = $categories_parents['parent_id']; } tep_db_free_result($categories_parents_result); // Housekeeping unset($multiple_category_ids); //Housekeeping $counted = count($category_parent); for($i=0; $i<$counted; $i++) { $current_category_id = $category_parent[$i]['categories_id']; //End Mod if (tep_not_null($current_category_id)) { $cp_size = sizeof($cPath_array); if ($cp_size == 0) { $cPath_new = $current_category_id; } else { $cPath_new = ''; if ( !isset($cats[($cp_size-1)]) ){ $last_category['parent_id'] = $parent_by_category[$cPath_array[($cp_size-1)]]; $cats[($cp_size-1)] = $last_category['parent_id']; } else { $last_category['parent_id'] = $cats[($cp_size-1)]; } if ( !isset($cats[(int)$current_category_id]) ){ $current_category['parent_id'] = $category_parent[$i]['parent_id']; $cats[(int)$current_category_id] = $current_category['parent_id']; } else { $current_category['parent_id'] = (int)$current_category_id; } if ($last_category['parent_id'] == $current_category['parent_id']) { for ($j=0; $j<($cp_size-1); $j++) { $cPath_new .= '_' . $cPath_array[$j]; } } else { for ($j=0; $j<$cp_size; $j++) { $cPath_new .= '_' . $cPath_array[$j]; } } $cPath_new .= '_' . $current_category_id; if (substr($cPath_new, 0, 1) == '_') { $cPath_new = substr($cPath_new, 1); $cPath_list[$category_parent[$i]['categories_id']] = 'cPath=' . $cPath_new; } } } else { $cPath_new = implode('_', $cPath_array); $cPath_list[$category_parent[$i]['categories_id']] = 'cPath=' . $cPath_new; } } unset($counted, $category_parent); return $cPath_list; } Ultimate SEO Urls 5 PRO - Multi Language Modern, Powerful SEO Urls KissMT Dynamic SEO Meta & Canonical Header Tags KissER Error Handling and Debugging KissIT Image Thumbnailer Security Pro - Querystring protection against hackers ( a KISS contribution ) If you found my post useful please click the "Like This" button to the right. Please only PM me for paid work. Link to comment Share on other sites More sharing options...
TracyS Posted March 23, 2007 Share Posted March 23, 2007 Are you still getting lots of .. select parent_id from categories where categories_id = '487' Especially when clicking a category that has multiple children? the problem is that catalog>index.php calls tep_get_path() in catalog>includes>functions>general.php for each link so there are multiple queries. I think I have it solved. Try the below: - Let me know how you get on WooHoo!!! :D :thumbsup: I tried it out and it appears to be working perfectly! THANK YOU!! That saved me over 20 queries on some pages :blush: I am going to run it by the SPPC thread to make sure I don't need any additional edits for the Hide Products or Categories from Customer Groups mod :thumbsup: ~Tracy Link to comment Share on other sites More sharing options...
New 
Recommended Posts
Archived
This topic is now archived and is closed to further replies.