Jan Zonjee Posted June 22, 2010 Share Posted June 22, 2010 But perhaps I should firstly focus on the index.php: I still have these ~200 queries, coming from "select c.categories..... where parent_id = 'xx', which is produced by some queries in the index.php. IMHO that comes from the category check, how many sub-cats are available, is the deepest end of the cats reached etc. Is it correct, that the cat query is querying all cat and sub-cats every time? Can I somehow optimize that ? Or is this quantity of queries only resulting from my quantity of categories? The select code from languages queries should be easy to solve (it that hasn't been done already in newer versions). The select c.categories..... where parent_id = 'xx' queries are typical for the drop-down in the categories box of the category box enhanced contribution. But you wouldn't see those if you commented out the inclusion of the categories box (unless you use an old version of the STS template contribution where the drop-down queries were executed even if you didn't use the drop-down). For the osC2.2 rc2a with standard install of products I found the following number of queries: index.php: 84, cPath=1: 125, product_info.php?products_id=20: 103 With the enhanced category box added: index.php: 104, cPath=1: 147, product_info.php?products_id=20: 126 (so about the number of categories higher...) With an adapted CountProductsStore and an adapted categories box (to use the CountProductsStore): index.php: 43, cPath=1: 43, product_info.php?products_id=20: 42: The adapted includes/classes/CountProductsStore.php (adapted from the optimize categories box contribution): <?php /* $Id: CountProductsStore.php v 1.2 2010/06/23 JanZ an object to store the number of products in a category and which category_id has which parent_id osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright (c) 2006 osCommerce Released under the GNU General Public License */ class CountProductsStore { var $prods_in_category, $category_tree, $category_name_tree; function CountProductsStore() { if (SHOW_COUNTS == 'true') { if (USE_PRODUCTS_COUNT_CACHE == 'true') { $this->prods_in_category = tep_cache_products_count(); } else { $category_query = tep_db_query("select categories_id from categories"); while ($_categories = tep_db_fetch_array($category_query)) { $categories[] = $_categories['categories_id']; } $products_query = tep_db_query("select count(*) as number_in_category, categories_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id in (" . implode(",", $categories) . ") group by categories_id"); while ($_prods_in_category = tep_db_fetch_array($products_query)) { $this->prods_in_category[$_prods_in_category['categories_id']] = $_prods_in_category['number_in_category']; } } } $category_trees_array = $this->buildCategoryTree(); $this->category_tree = $category_trees_array['parent_tree']; $this->category_name_tree = $category_trees_array['name_tree']; } function CountProductsInCategory($category_id) { if (isset($this->prods_in_category[$category_id])) { return $this->prods_in_category[$category_id]; } else { return 0; } } function hasChildCategories($category_id) { foreach ($this->category_tree as $categories_id => $parent_id) { if ($parent_id == $category_id) { $ChildCategories[] = $categories_id; } } // end foreach ($this->category_tree as $categories_id => $parent_id) if (isset($ChildCategories)) { return $ChildCategories; } else { return false; } } function buildCategoryTree() { $languages_id = ''; if (!isset($_SESSION['languages_id']) || $_SESSION['languages_id'] < 1) { $languages_id = 1; } else { $languages_id = $_SESSION['languages_id']; } $category_query = tep_db_query("select c.categories_id, c.parent_id, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name"); while ($categories = tep_db_fetch_array($category_query)) { $category_tree_array[$categories['categories_id']] = $categories['parent_id']; $category_name_tree_array[$categories['categories_id']] = $categories['categories_name']; } $categories_trees_array = array('parent_tree' => $category_tree_array, 'name_tree' => $category_name_tree_array); return $categories_trees_array; } function getParentCategory($category_id) { foreach ($this->category_tree as $categories_id => $parent_id) { if ($categories_id == $category_id) { return $parent_id; } } // end foreach ($this->category_tree as $categories_id => $parent_id) return false; } function countChildCategories($category_id) { $count = 0; foreach ($this->category_tree as $categories_id => $parent_id) { if ($parent_id == $category_id) { $count += 1; } } // end foreach ($this->category_tree as $categories_id => $parent_id) return $count; } function getChildCategories($category_id) { foreach ($this->category_tree as $categories_id => $parent_id) { if ($parent_id == $category_id) { $child_category[] = array('categories_id' => $categories_id, 'categories_name' => $this->category_name_tree[$categories_id]); } } // end foreach ($this->category_tree as $categories_id => $parent_id) return $child_category; } } ?> The adapted categories enhanced box: <?php /* $Id: categories.php,v 1.25 2003/07/09 01:13:58 hpdl Exp $ osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright (c) 2003 osCommerce Released under the GNU General Public License changed August 2003, by Nils Petersson contribution "Category Box Enhancement", version 1.1 version with enhanced CountProductsStore object */ function tep_show_category($counter) { // BoF - Contribution Category Box Enhancement 1.1 global $tree, $categories_string, $cPath_array, $cat_name; for ($i=0; $i<$tree[$counter]['level']; $i++) { $categories_string .= " "; } $cPath_new = 'cPath=' . $tree[$counter]['path']; if (isset($cPath_array) && in_array($counter, $cPath_array) && $cat_name == $tree[$counter]['name']) { //Link nicht anklickbar, wenn angewählt $categories_string .= '<a href="'; $categories_string .= tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">'; //Link nicht anklickbar, wenn angewählt } else { //Link nicht anklickbar, wenn angewählt $categories_string .= '<a href="'; $categories_string .= tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">'; } //Link nicht anklickbar, wenn angewählt if (tep_has_category_subcategories($counter)) { $categories_string .= tep_image(DIR_WS_IMAGES . 'pointer_blue.gif', ''); } else { $categories_string .= tep_image(DIR_WS_IMAGES . 'pointer_blue_light.gif', ''); } if (isset($cPath_array) && in_array($counter, $cPath_array)) { $categories_string .= '<b>'; } if ($cat_name == $tree[$counter]['name']) { $categories_string .= '<span class="errorText">'; } // display category name $categories_string .= $tree[$counter]['name']; if ($cat_name == $tree[$counter]['name']) { $categories_string .= '</span>'; } if (isset($cPath_array) && in_array($counter, $cPath_array)) { $categories_string .= '</b>'; } // EoF Category Box Enhancement $categories_string .= '</a>'; if (SHOW_COUNTS == 'true') { $products_in_category = tep_count_products_in_category($counter); if ($products_in_category > 0) { $categories_string .= ' (' . $products_in_category . ')'; } } $categories_string .= '<br>'; if ($tree[$counter]['next_id'] != false) { tep_show_category($tree[$counter]['next_id']); } } ?> <!-- categories //--> <tr> <td> <?php // BoF - Contribution Category Box Enhancement 1.1 if (isset($cPath_array)) { $cat_name = ''; for ($i=0, $n=sizeof($cPath_array); $i<$n; $i++) { if (isset($countproducts->category_name_tree[(int)$cPath_array[$i]])) { $cat_name = $countproducts->category_name_tree[(int)$cPath_array[$i]]; } } $cat_name = $categories['categories_name']; } // EoF Category Box Enhancement // display category name $info_box_contents = array(); $info_box_contents[] = array('text' => BOX_HEADING_CATEGORIES); new infoBoxHeading($info_box_contents, true, false); $categories_string = ''; $tree = array(); $ChildCategories = $countproducts->getChildCategories('0'); if (count($ChildCategories) > 0) { foreach ($ChildCategories as $chca_key => $id_name_array) { $tree[$id_name_array['categories_id']] = array('name' => $id_name_array['categories_name'], 'parent' => '0', 'level' => 0, 'path' => $id_name_array['categories_id'], 'next_id' => false); if (isset($parent_id)) { $tree[$parent_id]['next_id'] = $id_name_array['categories_id']; } $parent_id = $id_name_array['categories_id']; if (!isset($first_element)) { $first_element = $id_name_array['categories_id']; } } } // end if (count($ChildCategories) > 0) //------------------------ if (tep_not_null($cPath)) { $new_path = ''; reset($cPath_array); while (list($key, $value) = each($cPath_array)) { unset($parent_id); unset($first_id); $ChildCategories = $countproducts->getChildCategories((int)$value); if (count($ChildCategories) > 0) { $new_path .= $value; foreach ($ChildCategories as $chca_key => $id_name_array) { $tree[$id_name_array['categories_id']] = array('name' => $id_name_array['categories_name'], 'parent' => (int)$value, 'level' => $key+1, 'path' => $new_path . '_' . $id_name_array['categories_id'], 'next_id' => false); if (isset($parent_id)) { $tree[$parent_id]['next_id'] = $id_name_array['categories_id']; } $parent_id = $id_name_array['categories_id']; if (!isset($first_id)) { $first_id = $id_name_array['categories_id']; } $last_id = $id_name_array['categories_id']; } $tree[$last_id]['next_id'] = $tree[$value]['next_id']; $tree[$value]['next_id'] = $first_id; $new_path .= '_'; } else { break; } } // end while (list($key, $value) = each($cPath_array)) } tep_show_category($first_element); $info_box_contents = array(); $info_box_contents[] = array('text' => $categories_string); if ( (basename($PHP_SELF) != FILENAME_SPECIALS)) { $info_box_contents[] = array('align' => 'left', 'text' => '<font size=-2><b><a href="' . tep_href_link(FILENAME_SPECIALS, '', 'NONSSL') . '">' . BOX_INFORMATION_SPECIALS . '</a></b></font>'); }else{ $info_box_contents[] = array('align' => 'left', 'text' => '<font size=-2><b><a href="' . tep_href_link(FILENAME_SPECIALS, '', 'NONSSL') . '"><span class="errorText">' . BOX_INFORMATION_SPECIALS . '</a></b></font></span>'); } if ( (basename($PHP_SELF) != FILENAME_PRODUCTS_NEW)) { $info_box_contents[] = array('align' => 'left', 'text' => '<font size=-2><b><a href="' . tep_href_link(FILENAME_PRODUCTS_NEW, '', 'NONSSL') . '">' . BOX_INFORMATION_PRODUCTS_NEW . '</a></b></font>'); }else{ $info_box_contents[] = array('align' => 'left', 'text' => '<font size=-2><b><a href="' . tep_href_link(FILENAME_PRODUCTS_NEW, '', 'NONSSL') . '"><span class="errorText">' . BOX_INFORMATION_PRODUCTS_NEW . '</a></b></font></span>'); } function tep_get_paths($categories_array = '', $parent_id = '0', $indent = '', $path='') { global $languages_id, $countproducts; if (!is_array($categories_array)) $categories_array = array(); $ChildCategories = $countproducts->getChildCategories((int)$parent_id); foreach ($ChildCategories as $chca_key => $id_name_array) { if ($parent_id=='0'){ $categories_array[] = array('id' => $id_name_array['categories_id'], 'text' => $indent . $id_name_array['categories_name']); } else{ $categories_array[] = array('id' => $path . $parent_id . '_' .$id_name_array['categories_id'], 'text' => $indent . $id_name_array['categories_name']); } if ($id_name_array['categories_id'] != $parent_id) { $this_path=$path; if ($parent_id != '0') $this_path = $path . $parent_id . '_'; $categories_array = tep_get_paths($categories_array, $id_name_array['categories_id'], $indent . ' ', $this_path); } } return $categories_array; } $info_box_contents[] = array('form' => '<form action="' . tep_href_link(FILENAME_DEFAULT) . '" method="get">' . tep_hide_session_id(), 'align' => 'left', 'text' => '<b>' . BOX_DROPDOWN_MENUS . '<br>' . tep_draw_pull_down_menu('cPath', tep_get_paths(array(array('id' => '', 'text' => PULL_DOWN_DEFAULT))), $cPath, 'onchange="this.form.submit();" style="width: 100%"') ); new infoBox($info_box_contents); ?> </td> </tr> <!-- categories_eof //--> Link to comment Share on other sites More sharing options...
Andreas2003 Posted June 23, 2010 Share Posted June 23, 2010 Wow, Jan, your change brings a great enhancement ! Thanks in advance! With activated dropdown menu (former numbers where with inactive dropdown) I had 424 queries first, after the change it dropped to 235 ! But somewhere there seems to be some error, because I'm getting an error message: Warning: Invalid argument supplied for foreach() in /srv/www/includes/boxes/categories.php on line 186 In line 185-186 I have found this code: $ChildCategories = $countproducts->getChildCategories((int)$parent_id); foreach ($ChildCategories as $chca_key => $id_name_array) { This link here telling me, that an array is expected. So, according to the link, I changed the code to $ChildCategories = $countproducts->getChildCategories((int)$parent_id); if(empty($chca_key)) { return false; } foreach ($ChildCategories as $chca_key => $id_name_array) { and the error message is gone. But then, the dropdown menu is empty :blink: So, something seems to be wrong. The select code from languages queries should be easy to solve (it that hasn't been done already in newer versions). The select code from languages comes from my self-enhanced version of UltimateSEO. I have to solve this later, now I had it deactivated completely. The select c.categories..... where parent_id = 'xx' queries are typical for the drop-down in the categories box of the category box enhanced contribution. But you wouldn't see those if you commented out the inclusion of the categories box (unless you use an old version of the STS template contribution where the drop-down queries were executed even if you didn't use the drop-down). That is strange. I have never used any STS template, but as I said before, I saw this queries also with the cat box commented out ! I already made a comparison between my index.php and the vanilla-RC2a-index.php. Of course, I have changes in my version (got additionally HeaderTags, UltraPics and NewsDesk included), but the code is not so bloated up and no changes in the core cat-queries, which are included in the index.php. Any other suggestions? Link to comment Share on other sites More sharing options...
Andreas2003 Posted June 23, 2010 Share Posted June 23, 2010 That is strange. I have never used any STS template, but as I said before, I saw this queries also with the cat box commented out ! Got the sucker !! Woohooo !! It is not my index.php, that causing that query-trouble, but my header.php :angry: !! Thanks for clearing my mind with your mentioning of STS or other drop-down queries. I'm having a modified header as well with an included search within. There is also a dropdown menu with all cats in it. Sorry, sometimes you are not getting the point and not see the real problem still existing. Never thought of the header.php before, really sorry. But is it possible to "optimize" that dropdown as well ? The code in the header is that: <tr> <td class="infoBoxContents" align="middle" valign="middle" colspan="3"><?php echo BOX_ADVSEARCH_SEARCH ?></td> <td class="infoBoxContents" valign="middle" colspan="3"><?php echo tep_draw_hidden_field('search_in_description','1') . tep_draw_input_field('keywords', '', 'size="10" maxlength="30" style="width: ' . (BOX_WIDTH+100) . 'px"') ?></td> <td class="infoBoxContents" valign="top" colspan="3"> </td> <td class="infoBoxContents" valign="middle" colspan="3"><?php echo BOX_ADVSEARCH_CAT ?></td> <td class="infoBoxContents" valign="middle" colspan="3"><?php echo tep_draw_pull_down_menu('categories_id', tep_get_categories(array(array('id' => '', 'text' => BOX_ADVSEARCH_ALLCAT)))) ?></td> <td class="infoBoxContents" valign="top" colspan="3"> </td> <td class="infoBoxContents" valign="middle" colspan="3"><center><?php echo tep_image_submit('button_search.gif', BOX_HEADING_ADVSEARCH); ?></center></td> </tr> so especially this call is causing my trouble: tep_draw_pull_down_menu('categories_id', tep_get_categories(array(array('id' => '', 'text' => BOX_ADVSEARCH_ALLCAT)))) Link to comment Share on other sites More sharing options...
Jan Zonjee Posted June 23, 2010 Share Posted June 23, 2010 But somewhere there seems to be some error, because I'm getting an error message: Warning: Invalid argument supplied for foreach() in /srv/www/includes/boxes/categories.php on line 186 In line 185-186 I have found this code: $ChildCategories = $countproducts->getChildCategories((int)$parent_id); foreach ($ChildCategories as $chca_key => $id_name_array) { Ah, I didn't get that error so I thought it would be OK to leave it like that. It was way past my bedtime too :) Just wrap it around an if statement that looks if there actually is an array: function tep_get_paths($categories_array = '', $parent_id = '0', $indent = '', $path='') { global $languages_id, $countproducts; if (!is_array($categories_array)) $categories_array = array(); $ChildCategories = $countproducts->getChildCategories((int)$parent_id); if (count($ChildCategories) > 0) { foreach ($ChildCategories as $chca_key => $id_name_array) { if ($parent_id=='0'){ $categories_array[] = array('id' => $id_name_array['categories_id'], 'text' => $indent . $id_name_array['categories_name']); } else{ $categories_array[] = array('id' => $path . $parent_id . '_' .$id_name_array['categories_id'], 'text' => $indent . $id_name_array['categories_name']); } if ($id_name_array['categories_id'] != $parent_id) { $this_path=$path; if ($parent_id != '0') $this_path = $path . $parent_id . '_'; $categories_array = tep_get_paths($categories_array, $id_name_array['categories_id'], $indent . ' ', $this_path); } } // end foreach ($ChildCategories as $chca_key => $id_name_array) } // end if (count($ChildCategories) > 0) return $categories_array; } Link to comment Share on other sites More sharing options...
Jan Zonjee Posted June 23, 2010 Share Posted June 23, 2010 But is it possible to "optimize" that dropdown as well ? Yes, actually the code for the function tep_get_categories is very much alike the code for tep_get_paths as in categories box enhanced. This is a version that will do the queries if $countproducts isn't an object but uses the object when available (zero queries!). function tep_get_categories($categories_array = '', $parent_id = '0', $indent = '') { global $languages_id, $countproducts; if (!is_array($categories_array)) $categories_array = array(); if (isset($countproducts) && is_object($countproducts)) { $ChildCategories = $countproducts->getChildCategories((int)$parent_id); if (count($ChildCategories) > 0) { foreach ($ChildCategories as $chca_key => $id_name_array) { $categories_array[] = array('id' => $id_name_array['categories_id'], 'text' => $indent . $id_name_array['categories_name']); if ($id_name_array['categories_id'] != $parent_id) { $categories_array = tep_get_categories($categories_array, $id_name_array['categories_id'], $indent . ' '); } } // end foreach ($ChildCategories as $chca_key => $id_name_array) } // end if (count($ChildCategories) > 0) } else { $categories_query = tep_db_query("select c.categories_id, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where parent_id = '" . (int)$parent_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name"); while ($categories = tep_db_fetch_array($categories_query)) { $categories_array[] = array('id' => $categories['categories_id'], 'text' => $indent . $categories['categories_name']); if ($categories['categories_id'] != $parent_id) { $categories_array = tep_get_categories($categories_array, $categories['categories_id'], $indent . ' '); } } } return $categories_array; } Note that with the changed version of CountProductsStore.php you are now using caching of the object is probably not an option. Haven't looked at that yet. Due to using KissER Error Handling & Debugging instead of the Queries Debug contribution another little flaw was found. The function getChildCategories in CountProductsStore.php needs a little change: function getChildCategories($category_id) { $child_category = array(); // added this line foreach ($this->category_tree as $categories_id => $parent_id) { if ($parent_id == $category_id) { $child_category[] = array('categories_id' => $categories_id, 'categories_name' => $this->category_name_tree[$categories_id]); } } // end foreach ($this->category_tree as $categories_id => $parent_id) return $child_category; } // end function getChildCategories($category_id) Another note. I found that changing the language would not result in an immediate change in the language of the menu and drop-downs. That would happen on the next click. This is because the language is set after countproducts is instantiated. Moving that down a few lines helped: // include the language translations require(DIR_WS_LANGUAGES . $language . '.php'); // include CountProductsStore object require(DIR_WS_CLASSES . 'CountProductsStore.php'); $countproducts = new CountProductsStore(); // currency Link to comment Share on other sites More sharing options...
Andreas2003 Posted June 24, 2010 Share Posted June 24, 2010 Ah, I didn't get that error so I thought it would be OK to leave it like that. It was way past my bedtime too :) Just wrap it around an if statement that looks if there actually is an array: ......... Jan, thanks you so much for your support, works great! Another note. I found that changing the language would not result in an immediate change in the language of the menu and drop-downs. That would happen on the next click. This is because the language is set after countproducts is instantiated. Moving that down a few lines helped: Cool! But also a sidenote, first I moved up the language-include by error, which did not help. Moving down as you said has done the trick. Due to using KissER Error Handling & Debugging instead of the Queries Debug contribution another little flaw was found. The function getChildCategories in CountProductsStore.php needs a little change: Is this code-change only required by KissER Error .... contrib? Is the change also useful, if only the Queries debug contrib is applied? Now to the corrected queries within my header: your code-change in function tep_get_categories... Note that with the changed version of CountProductsStore.php you are now using caching of the object is probably not an option. Haven't looked at that yet. Worked also great with no additional queries, thanks again. But can you explain that a little bit more, please? Just for understanding, what that mean in detail. Link to comment Share on other sites More sharing options...
Jan Zonjee Posted June 24, 2010 Share Posted June 24, 2010 Is this code-change only required by KissER Error .... contrib? Is the change also useful, if only the Queries debug contrib is applied? No, I used that instead of Queries debug. A bit simpler to install and it also keeps track of errors (some of which may go unnoticed as the one in that function, it was a E_NOTICE error but still better to solve it). I wouldn't add it now that you have Queries debug installed. Now to the corrected queries within my header: Worked also great with no additional queries, thanks again. But can you explain that a little bit more, please? Just for understanding, what that mean in detail. In the countproducts the whole category setup for a particular language is stored, in the sort order that comes from the database. With some simple functions this object is used to see if a category has child categories or which parent category it has instead of querying the database for that information. With 20 categories the number of queries is small enough to not really be noticed but with 200 queries (or with a slow server) those queries add up in time. So everything is now done in PHP instead of PHP going to the MySQL database for a query and get the results, process them etc., then again, and again and again etc. Link to comment Share on other sites More sharing options...
Andreas2003 Posted June 24, 2010 Share Posted June 24, 2010 Alright, thanks. And what did you meant with that? Note that with the changed version of CountProductsStore.php you are now using caching of the object is probably not an option. Haven't looked at that yet. Any disadvantages in caching? Although I would like to get away from caching anyway... Link to comment Share on other sites More sharing options...
Jan Zonjee Posted June 24, 2010 Share Posted June 24, 2010 Any disadvantages in caching? Although I would like to get away from caching anyway... Sorry, I'm mistaken. Have looked at it a little better but the CountProductsStore.php file only can cache the products count (that you switched off). The additional functions that were added in the CountProductsStore.php posted above are not affected by that. This particular cache is independent of all the other (three I believe) caches. The original caching functions of osC are not bad or detrimental although with 200 categories you probably get a lot of cache files... Caching doesn't always help. E.g. a new visitor that gets the osCsid appended to links doesn't benefit from the cached categories of osC. Link to comment Share on other sites More sharing options...
Andreas2003 Posted June 25, 2010 Share Posted June 25, 2010 Ah, ok. But now, I think, I have some strange settings set in my admin >_< Normal osC cache function is set to off. Page cache function from Chemo is set to off. Category count function is set to off BUT category count cache is set to on :lol: But anyway, I don't think, that should be a matter. Jan, thank you so much for your patience and support !! What a pity, that there is no "thank you"-function in this forum. Thanks again. Link to comment Share on other sites More sharing options...
Andreas2003 Posted June 28, 2010 Share Posted June 28, 2010 Another note. I found that changing the language would not result in an immediate change in the language of the menu and drop-downs. That would happen on the next click. This is because the language is set after countproducts is instantiated. Moving that down a few lines helped: // include the language translations require(DIR_WS_LANGUAGES . $language . '.php'); // include CountProductsStore object require(DIR_WS_CLASSES . 'CountProductsStore.php'); $countproducts = new CountProductsStore(); // currency Ah, Jan, sorry for coming up again, but maybe this is not finally solved, because I got feedback from visitors, that the site itself shows content in german, but the categories in english. Caching is disabled, all old cache files are deleted (if this might be a problem). The change you mentioned is done by moving the count-require down under the language-require. May I send you the beginning of my application_top per PM for a quick review ? Thanks in advance, Regards Andreas Link to comment Share on other sites More sharing options...
Jack_mcs Posted August 12, 2010 Share Posted August 12, 2010 For those using the Queries Debug contribution, you may be interested in Queries Debug GUI. Support Links: For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc. Get the latest versions of my addons Recommended SEO Addons Link to comment Share on other sites More sharing options...
♥stubbsy Posted October 16, 2010 Share Posted October 16, 2010 Hi all, I'm having a play around with the multi stores contribution and have a problem query when searching which is taking between 5 and 7 seconds on a dedicated server. I was wondering if anyone had a few hours spare and fancied a challenge ;) any thoughts on this one... [19] => select distinct p.products_image, m.manufacturers_name, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status = '1' AND s.stores_id = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = '1' AND s.stores_id = '1', s.specials_new_products_price, p.products_price) as final_price from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id and s.stores_id = '1' INNER JOIN products_to_stores p2s on p.products_id = s.products_id left join products_attributes pa on p.products_id = pa.products_id left join products_options po on pa.options_id = po.products_options_id left join products_options_values pov on pa.options_values_id = pov.products_options_values_id, products_description pd, categories c, products_to_categories p2c where p2s.stores_id = '1' and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%baller%' or p.products_model like '%search term%' or m.manufacturers_name like '%search term%' or po.products_options_name like '%search term%' or pov.products_options_values_name like '%search term%') ) order by pd.products_name limit 0, 30 Cheers Dave Link to comment Share on other sites More sharing options...
Jan Zonjee Posted October 16, 2010 Share Posted October 16, 2010 any thoughts on this one... Did you add the index on the table specials? Link to comment Share on other sites More sharing options...
♥stubbsy Posted October 18, 2010 Share Posted October 18, 2010 Hi Jan, Thanks for the quick reply, seems I'd already implemented that index sometime in the past All the best Dave Link to comment Share on other sites More sharing options...
spoofy Posted October 25, 2010 Share Posted October 25, 2010 There was code here somewhere either in this thread or somewhere else. If anyone can help I would appreciate it. The code, I think was for, storing the configuration and/or settings in a file every so often by retrieving it from the database thus helping the page loads overall. My Contributions: Google XML Sitemap SEO compatible with Ultimate SEO URL by FWR Media ::: Accurate & Precise Bread Crumb Trail Link to comment Share on other sites More sharing options...
spoofy Posted October 25, 2010 Share Posted October 25, 2010 There was code here somewhere either in this thread or somewhere else. If anyone can help I would appreciate it. The code, I think was for, storing the configuration and/or settings in a file every so often by retrieving it from the database thus helping the page loads overall. Nevermind found it: http://www.oscommerce.com/community/contributions,1862 My Contributions: Google XML Sitemap SEO compatible with Ultimate SEO URL by FWR Media ::: Accurate & Precise Bread Crumb Trail Link to comment Share on other sites More sharing options...
addicted Posted October 31, 2010 Share Posted October 31, 2010 Hello I would like to move static images (product images, template images etc) on a separate host How can it be done ? just replacing in /includes/configure.php ? Any help will be appriciate Link to comment Share on other sites More sharing options...
pdcelec Posted November 9, 2010 Share Posted November 9, 2010 Hi I have made these changes and it has made an amazing difference in queries and speed. The slowest part of my site now is the stylesheet loading. Is there a way to cache the stylesheet? Or is that just taking things too far? Link to comment Share on other sites More sharing options...
discxpress Posted December 20, 2010 Share Posted December 20, 2010 Hello, I ran the debugger and the following query takes 5 to 7 seconds to load. [112] => select distinct p.products_id, pd.products_name from products p, products_description pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_ordered desc, pd.products_name limit 10 Can this be further optimized? Any help would be appreciated. Link to comment Share on other sites More sharing options...
Jan Zonjee Posted December 20, 2010 Share Posted December 20, 2010 The slowest part of my site now is the stylesheet loading. Is there a way to cache the stylesheet? Or is that just taking things too far? The browser of the client is caching the stylesheet. Since it is a static file there is nothing to cache server side. Link to comment Share on other sites More sharing options...
Jan Zonjee Posted December 20, 2010 Share Posted December 20, 2010 I ran the debugger and the following query takes 5 to 7 seconds to load. [112] => select distinct p.products_id, pd.products_name from products p, products_description pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_ordered desc, pd.products_name limit 10 Can this be further optimized? If you can find where this query comes from you could try rewriting it to only use the table products: select distinct p.products_id from products p where p.products_status = '1' and p.products_ordered > 0 order by p.products_ordered desc limit 10 Then retrieve the products_id's and query for the products_name from the table products_description. Then combine the results in an array and use that for the module/box where this code is coming from. Since the tables to work with are now smaller (not the combination of products and products_description) that should be faster. Link to comment Share on other sites More sharing options...
Debs Posted December 20, 2010 Share Posted December 20, 2010 Hi I have made these changes and it has made an amazing difference in queries and speed. The slowest part of my site now is the stylesheet loading. Is there a way to cache the stylesheet? Or is that just taking things too far? There are a few ways to easily accomplish this (compress and sent proper headers)... This is the easiest for most people: ### add this to your site root htaccess AddHandler application/x-httpd-php .css .js ### Use this instead (below) if needed - PHP5 as default ### AddHandler application/x-httpd-php5 .css .js ### end of htaccess edit Then place this at the very TOP of your stylesheet (EVERY stylesheet on your site and admin) <?php ob_start ("ob_gzhandler"); header("Content-type: text/css"); header("Cache-Control: must-revalidate"); $offset = 60 * 60 * 24 * 3; $ExpStr = "Expires: " . gmdate("D, d M Y H:i:s", time() + $offset) . " GMT"; Header($ExpStr); ?> Place this rule at the top, in EVERY javascript file <?php ob_start('ob_gzhandler'); header("Content-type: text/javascript"); header('Cache-Control: must-revalidate'); header('Expires: '.gmdate('D, d M Y 23:59:59', strtotime('+1 week')).' GMT'); ob_start('compress'); function compress($buffer){ $buffer = preg_replace("/((?:\/\*(?:[^*]|(?:\*+[^*\/]))*\*+\/)|(?:\/\/.*))/", "", $buffer); $buffer = str_replace(array("\r\r\r","\r\r","\r\n","\n\r","\n\n\n","\n\n"),"\n",$buffer); return $buffer;} ?> NOTE: If your javascript is not formated correctly, you can remove this part of the script that you placed at the top of your javascript files to (used only to further compress): ob_start('compress'); function compress($buffer){ $buffer = preg_replace("/((?:\/\*(?:[^*]|(?:\*+[^*\/]))*\*+\/)|(?:\/\/.*))/", "", $buffer); $buffer = str_replace(array("\r\r\r","\r\r","\r\n","\n\r","\n\n\n","\n\n"),"\n",$buffer); return $buffer;} You would then only have this at the top of your javascript files: <?php ob_start('ob_gzhandler'); header("Content-type: text/javascript"); header('Cache-Control: must-revalidate'); header('Expires: '.gmdate('D, d M Y 23:59:59', strtotime('+1 week')).' GMT'); ?> If you only wish to compress your stylesheets.... then remove the .js from the addhandler and only mod your stylesheets. ### add this to your site root htaccess AddHandler application/x-httpd-php .css This will server up compressed files to every browser that supports such. You should see your files are a fraction of the size. To check that you are serving compressed files, check your site here: http://www.websiteoptimization.com/services/analyze/ and view the results. Kind regards, Debs Link to comment Share on other sites More sharing options...
pdcelec Posted December 20, 2010 Share Posted December 20, 2010 What I have in my htacess is this # BEGIN Expire headers ExpiresActive On ExpiresDefault "access plus 1 seconds" ExpiresByType image/jpeg "access plus 2592000 seconds" ExpiresByType image/png "access plus 2592000 seconds" ExpiresByType image/gif "access plus 2592000 seconds" ExpiresByType image/x-icon "access plus 2592000 seconds" ExpiresByType text/css "access plus 604800 seconds" ExpiresByType text/javascript "access plus 604800 seconds" ExpiresByType application/javascript "access plus 604800 seconds" ExpiresByType text/html "access plus 2592000 seconds" # END Expire headers Would I need to also add the last post? Link to comment Share on other sites More sharing options...
Debs Posted December 20, 2010 Share Posted December 20, 2010 What I have in my htacess is this # BEGIN Expire headers ExpiresActive On ExpiresDefault "access plus 1 seconds" ExpiresByType image/jpeg "access plus 2592000 seconds" ExpiresByType image/png "access plus 2592000 seconds" ExpiresByType image/gif "access plus 2592000 seconds" ExpiresByType image/x-icon "access plus 2592000 seconds" ExpiresByType text/css "access plus 604800 seconds" ExpiresByType text/javascript "access plus 604800 seconds" ExpiresByType application/javascript "access plus 604800 seconds" ExpiresByType text/html "access plus 2592000 seconds" # END Expire headers Would I need to also add the last post? The last post (by me) would also compress your files. GZip compression. This means you can drastically reduce the size of all those bloated css and javascript files. No you can not use your .js and .css expire headers at the same time, as they are included in the code, if used in the post above. It is not needed unless you are trying to reduce your page size and speed. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.