Jump to content

Archived

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

Chemo

A Store Speed Optimization in Progress

Recommended Posts

Thanks. It works out just fine. It loads fast. With the code you suggested the page loads Current Parse Time: 0.835 s with 367 queries compared to 20.176s with 367 queries.

 

Thanks again. Is there any place where I can begin to learn how to code php? I really need to learn.

 

Oh just 1800% faster then :blink:

 

Glad it helped you out.

Share this post


Link to post
Share on other sites

Can anyone tell me how to get rid of these ones ?

[9] => select startdate, counter from counter

[10] => update counter set counter = '26614'

[11] => select count(*) as total from sessions where sesskey = 'gr486ea4cvuno8a9o3m9lg0gb3'

 

And this one:

[3] => select count(*) as count from whos_online where session_id = 'gr486ea4cvuno8a9o3m9lg0gb3'

[4] => update whos_online set customer_id = '0', full_name = 'Guest', ip_address = '**********', time_last_click = '1259052372', last_page_url = '/' where session_id = 'gr486ea4cvuno8a9o3m9lg0gb3'

Share this post


Link to post
Share on other sites

Indeed, but it actually replicates the same thing that tep_get_category_tree does. Using a slightly adapted tep_get_category_tree optimization seems to give the same results but should be producing a lot less queries.

 

Add to includes/functions/general.php:

 function tep_get_subcategory_tree($parent_id = '0', $spacing = '', $exclude = '', $category_tree_array = '', $include_itself = false) {
   global $languages_id;

   if (!is_array($category_tree_array)) $category_tree_array = array();
   if ( (sizeof($category_tree_array) < 1) && ($exclude != '0') ) $category_tree_array[] = array('id' => '0', 'text' => TEXT_TOP);

   if ($include_itself) {
     $category_query = tep_db_query("select cd.categories_name from " . TABLE_CATEGORIES_DESCRIPTION . " cd where cd.language_id = '" . (int)$languages_id . "' and cd.categories_id = '" . (int)$parent_id . "'");
     $category = tep_db_fetch_array($category_query);
     $category_tree_array[] = array('id' => $parent_id, 'text' => $category['categories_name']);
   }

   $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' and c.parent_id = '" . (int)$parent_id . "' order by c.sort_order, cd.categories_name");
   while ($categories = tep_db_fetch_array($categories_query)) {
     if ($exclude != $categories['categories_id']) $category_tree_array[] = array('id' => $categories['categories_id'], 'text' => $spacing . $categories['categories_name']);
     $category_tree_array = tep_get_category_tree($categories['categories_id'], $spacing . '   ', $exclude, $category_tree_array);
   }

   return $category_tree_array;
 }

   function tep_get_category_tree($parent_id = '0', $spacing = '', $exclude = '', $category_tree_array = '', $include_itself = false) {
   global $languages_id;

   if (!is_array($category_tree_array)) $category_tree_array = array();
   if ( (sizeof($category_tree_array) < 1) && ($exclude != '0') ) $category_tree_array[] = array('id' => '0', 'text' => BOX_ALL_CATEGORIES);

   $category_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id, pcategories.parent_id as grand_parent_id from " . TABLE_CATEGORIES . " c  left join " . TABLE_CATEGORIES . " AS pcategories on pcategories.categories_id = c.parent_id," . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by grand_parent_id, c.parent_id, c.sort_order, cd.categories_name");

   while ($categories = tep_db_fetch_array($category_query)) {
$_category_tree_array[] = array('grand_parent_id' => $categories['grand_parent_id'], 'parent_id' => $categories['parent_id'], 'id' => $categories['categories_id'], 'text' => ($categories['parent_id'] != '0' ? "   ".$categories['categories_name']: $categories['categories_name']));  
   } 

   if ($parent_id == '0') {
   foreach ($_category_tree_array as $index_of => $sub_array) {
    if (!tep_not_null($sub_array['grand_parent_id'])) {
	    $parent_categories[] = array('id' => $sub_array['id'], 'text' =>  $sub_array['text']);		   
      }
   } // end foreach ($_category_tree_array as $index_of => $sub_array)
   } else { // parent_id is set, get a subset (used for deleting a category, with subcats and products)
 foreach ($_category_tree_array as $index_of => $sub_array) {
    if ($sub_array['id'] == $parent_id) {
	    $parent_categories[] = array('id' => $sub_array['id'], 'text' =>  $sub_array['text']);		   
      }
   } // end foreach ($_category_tree_array as $index_of => $sub_array)
   }

   for ($x = 0; $x < count($parent_categories); $x++) {
    array_push($category_tree_array, $parent_categories[$x]);
    foreach($_category_tree_array as $index_of_cta => $sub_array_cta) {
	    if ($sub_array_cta['parent_id'] == $parent_categories[$x]['id']) {
		    array_push($category_tree_array, array_slice($_category_tree_array[$index_of_cta], 2));
		    // BOF insert subsubcategories and everything below that
		    $categories_counter = $sub_array_cta['id'];
		    // delete the sorted category from the array, less to loop through
		    unset($_category_tree_array[$index_of_cta]);
		    $add_more_subcategories = tep_add_subcategory_to_tree($category_tree_array, $_category_tree_array, $parent_categories, $categories_counter, '2');
		    $category_tree_array = $add_more_subcategories['cat_tree'];
		    $_category_tree_array = $add_more_subcategories['_cat_tree'];
		    // EOF insert subsubcategories and everything below that
	    }
    }
   }
   return $category_tree_array;
 } 

 function tep_add_subcategory_to_tree($category_tree_array, $_category_tree_array, $parent_categories, $categories_counter, $level) {
  foreach($_category_tree_array as $index_of_cta => $sub_array_cta) {
	    if ($sub_array_cta['parent_id'] ==  $categories_counter && !in_array($categories_counter, $parent_categories) ) {
		    $subcategory_to_add = array_slice($_category_tree_array[$index_of_cta],2);
		    $spacing = '';
		    for ($i = 1; $i < $level ; $i++) { $spacing .= "   "; }
		    $subcategory_to_add['text'] = $spacing.$subcategory_to_add['text'];
		    array_push($category_tree_array, $subcategory_to_add);
		    $cat_counter = $sub_array_cta['id'];
		    // delete the sorted category from the array, less to loop through
		    unset($_category_tree_array[$index_of_cta]);
		    // tep_add_subcategory_to_tree keeps calling itself to add the subcategories
		    $add_more_subcategories = tep_add_subcategory_to_tree($category_tree_array, $_category_tree_array, $parent_categories, $cat_counter, $level+1);
		    $category_tree_array = $add_more_subcategories['cat_tree'];
		    $_category_tree_array = $add_more_subcategories['_cat_tree'];
	    }
    }
    $resultant = array('cat_tree' => $category_tree_array, '_cat_tree' => $_category_tree_array);
    return $resultant;
 } // end function tep_add_subcategory_to_tree

Note that the original tep_get_category_tree uses TEXT_TOP for the first entry in the dropdown menu. Changed that to BOX_ALL_CATEGORIES that you use.

 

Change:

<?php echo tep_draw_form('search', tep_href_link(FILENAME_ADVANCED_SEARCH_RESULT, '', 'NONSSL', false), 'get') . tep_draw_hidden_field('search_in_description','1') . tep_draw_input_field('keywords', 'enter keywords', 'size="10" maxlength="30" style="width: ' . (BOX_WIDTH+150) . 'px"') . ' ' . tep_draw_pull_down_menu('categories_id', tep_get_categories(array(array('parent_id' => '', 'text' => BOX_ALL_CATEGORIES)))) . ' ' . tep_hide_session_id() . tep_image_submit('button_search.gif', IMAGE_BUTTON_SEARCH, 'align="middle"') . '</form>'; ?></td>

To:

<?php echo tep_draw_form('search', tep_href_link(FILENAME_ADVANCED_SEARCH_RESULT, '', 'NONSSL', false), 'get') . tep_draw_hidden_field('search_in_description','1') . tep_draw_input_field('keywords', 'enter keywords', 'size="10" maxlength="30" style="width: ' . (BOX_WIDTH+150) . 'px"') . ' ' . tep_draw_pull_down_menu('categories_id', tep_get_category_tree(), '0') . ' ' . tep_hide_session_id() . tep_image_submit('button_search.gif', IMAGE_BUTTON_SEARCH, 'align="middle"') . '</form>'; ?></td>

Jan

The fix for the search box doesn't work properly. It doesn't return any products in the search results.

 

Thanks

Share this post


Link to post
Share on other sites

The fix for the search box doesn't work properly. It doesn't return any products in the search results.

The only change that this code introduced was changing the first option in the dropdown friom <option value="" SELECTED>All categories</option> to <option value="0" SELECTED>All categories</option>

 

That change might be the reason. You can fix that by changing the function tep_get_category_tree in you catalog/include/functions/general.php

 

Your function has a few lines into the code:

 

   if ( (sizeof($category_tree_array) < 1) && ($exclude != '0') ) $category_tree_array[] = array('id' => '0', 'text' => BOX_ALL_CATEGORIES);

If you change it to:

   if ( (sizeof($category_tree_array) < 1) && ($exclude != '0') ) $category_tree_array[] = array('id' => '', 'text' => BOX_ALL_CATEGORIES);

the code it outputs is exactly the same as you had so I suppose it should work again.

Share this post


Link to post
Share on other sites

I am just starting to look into optimizing my store and am looking for advice on where and how to start. I did the tax class thing and it seemed to make a decent improvement but I definitely have a long way to go. I have tried reading through many pages of this thread but I am finding the advice is either above my current experience or is talked about but isn't shown how to be done.

 

Here is where I am at right now, when I get into the pages that display products etc I am getting over 1000 queries

 

Current Parse Time: 2.363 s with 1014 queries

 

Product Info pages I am getting the below

 

Current Parse Time: 1.009 s with 55 queries

 

Index.php

 

Current Parse Time: 0.105 s with 60 queries

QUERY DEBUG:

 

Array

(

[QUERIES] => Array

(

[0] => select tax_description from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '0') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '0') and tr.tax_class_id = '0' order by tr.tax_priority

[1] => select value from sessions where sesskey = 'a3a0d3985787720c3edf9bf93f2bd0fe' and expiry > '1262733421'

[2] => select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies

[3] => select languages_id, name, code, image, directory from languages order by sort_order

[4] => delete from whos_online where time_last_click < '1262732521'

[5] => select count(*) as count from whos_online where session_id = 'a3a0d3985787720c3edf9bf93f2bd0fe'

[6] => insert into whos_online (customer_id, full_name, session_id, ip_address, time_entry, time_last_click, last_page_url) values ('0', 'Guest', 'a3a0d3985787720c3edf9bf93f2bd0fe', '67.71.41.199', '1262733421', '1262733421', '/store/')

[7] => select banners_id, date_scheduled from banners where date_scheduled != ''

[8] => select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id

[9] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0

[10] => select products_id, products_image, products_tax_class_id, products_price from products where products_status = '1' order by products_date_added desc limit 10

[11] => select products_name from products_description where products_id = '1035' and language_id = '1'

[12] => select products_price, products_model from products where products_id = '1035'

[13] => select specials_new_products_price from specials where products_id = '1035' and status

[14] => select categories_id from products_to_categories where products_id = '1035'

[15] => select sale_specials_condition, sale_deduction_value, sale_deduction_type from salemaker_sales where sale_categories_all like '%,187,%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0000-00-00') and (sale_date_end >= now() or sale_date_end = '0000-00-00') and (sale_pricerange_from <= '335.0000' or sale_pricerange_from = '0') and (sale_pricerange_to >= '335.0000' or sale_pricerange_to = '0')

[16] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '38') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '74') and tr.tax_class_id = '5' group by tr.tax_priority

[17] => select products_id, products_image, products_tax_class_id, products_price from products where products_status = '1' order by products_date_added desc limit 6

[18] => select products_price, products_model from products where products_id = '1044'

[19] => select specials_new_products_price from specials where products_id = '1044' and status

[20] => select categories_id from products_to_categories where products_id = '1044'

[21] => select sale_specials_condition, sale_deduction_value, sale_deduction_type from salemaker_sales where sale_categories_all like '%,137,%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0000-00-00') and (sale_date_end >= now() or sale_date_end = '0000-00-00') and (sale_pricerange_from <= '120.0000' or sale_pricerange_from = '0') and (sale_pricerange_to >= '120.0000' or sale_pricerange_to = '0')

[22] => select products_name from products_description where products_id = '1044' and language_id = '1'

[23] => select products_price, products_model from products where products_id = '1043'

[24] => select specials_new_products_price from specials where products_id = '1043' and status

[25] => select categories_id from products_to_categories where products_id = '1043'

[26] => select sale_specials_condition, sale_deduction_value, sale_deduction_type from salemaker_sales where sale_categories_all like '%,167,%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0000-00-00') and (sale_date_end >= now() or sale_date_end = '0000-00-00') and (sale_pricerange_from <= '265.0000' or sale_pricerange_from = '0') and (sale_pricerange_to >= '265.0000' or sale_pricerange_to = '0')

[27] => select products_name from products_description where products_id = '1043' and language_id = '1'

[28] => select products_price, products_model from products where products_id = '1042'

[29] => select specials_new_products_price from specials where products_id = '1042' and status

[30] => select categories_id from products_to_categories where products_id = '1042'

[31] => select sale_specials_condition, sale_deduction_value, sale_deduction_type from salemaker_sales where sale_categories_all like '%,135,%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0000-00-00') and (sale_date_end >= now() or sale_date_end = '0000-00-00') and (sale_pricerange_from <= '265.0000' or sale_pricerange_from = '0') and (sale_pricerange_to >= '265.0000' or sale_pricerange_to = '0')

[32] => select products_name from products_description where products_id = '1042' and language_id = '1'

[33] => select products_price, products_model from products where products_id = '1041'

[34] => select specials_new_products_price from specials where products_id = '1041' and status

[35] => select categories_id from products_to_categories where products_id = '1041'

[36] => select sale_specials_condition, sale_deduction_value, sale_deduction_type from salemaker_sales where sale_categories_all like '%,62,%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0000-00-00') and (sale_date_end >= now() or sale_date_end = '0000-00-00') and (sale_pricerange_from <= '265.0000' or sale_pricerange_from = '0') and (sale_pricerange_to >= '265.0000' or sale_pricerange_to = '0')

[37] => select products_name from products_description where products_id = '1041' and language_id = '1'

[38] => select products_price, products_model from products where products_id = '1040'

[39] => select specials_new_products_price from specials where products_id = '1040' and status

[40] => select categories_id from products_to_categories where products_id = '1040'

[41] => select sale_specials_condition, sale_deduction_value, sale_deduction_type from salemaker_sales where sale_categories_all like '%,111,%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0000-00-00') and (sale_date_end >= now() or sale_date_end = '0000-00-00') and (sale_pricerange_from <= '265.0000' or sale_pricerange_from = '0') and (sale_pricerange_to >= '265.0000' or sale_pricerange_to = '0')

[42] => select products_name from products_description where products_id = '1040' and language_id = '1'

[43] => select products_price, products_model from products where products_id = '1039'

[44] => select specials_new_products_price from specials where products_id = '1039' and status

[45] => select categories_id from products_to_categories where products_id = '1039'

[46] => select sale_specials_condition, sale_deduction_value, sale_deduction_type from salemaker_sales where sale_categories_all like '%,108,%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0000-00-00') and (sale_date_end >= now() or sale_date_end = '0000-00-00') and (sale_pricerange_from <= '295.0000' or sale_pricerange_from = '0') and (sale_pricerange_to >= '295.0000' or sale_pricerange_to = '0')

[47] => select products_name from products_description where products_id = '1039' and language_id = '1'

[48] => select p.products_id, pd.products_name, products_date_available as date_expected from products p, products_description pd where to_days(products_date_available) >= to_days(now()) and p.products_id = pd.products_id and pd.language_id = '1' order by date_expected desc limit 10

[49] => 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

[50] => select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added desc limit 10

[51] => select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from reviews r, reviews_description rd, products p, products_description pd where p.products_status = '1' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and rd.languages_id = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by r.reviews_id desc limit 10

[52] => select substring(reviews_text, 1, 60) as reviews_text from reviews_description where reviews_id = '3' and languages_id = '1'

[53] => select startdate, counter from counter

[54] => update counter set counter = '668047'

[55] => select banners_id, banners_title, banners_image, banners_html_text from banners where status = '1' and banners_group = '468x50'

[56] => select count(*) as count from banners_history where banners_id = '1' and date_format(banners_history_date, '%Y%m%d') = date_format(now(), '%Y%m%d')

[57] => update banners_history set banners_shown = banners_shown + 1 where banners_id = '1' and date_format(banners_history_date, '%Y%m%d') = date_format(now(), '%Y%m%d')

[58] => select count(*) as total from sessions where sesskey = 'a3a0d3985787720c3edf9bf93f2bd0fe'

[59] => update sessions set expiry = '1262734861', value = 'cart|O:12:\"shoppingCart\":5:{s:8:\"contents\";a:0:{}s:5:\"total\";i:0;s:6:\"weight\";i:0;s:12:\"content_type\";b:0;s:9:\"shiptotal\";s:0:\"\";}language|s:7:\"english\";languages_id|s:1:\"1\";currency|s:3:\"CAD\";navigation|O:17:\"navigationHistory\":2:{s:4:\"path\";a:1:{i:0;a:4:{s:4:\"page\";s:9:\"index.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:0:{}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:0:{}}' where sesskey = 'a3a0d3985787720c3edf9bf93f2bd0fe'

)

 

[TIME] => Array

(

[0] => 0.000778

[1] => 0.000415

[2] => 0.000266

[3] => 0.000362

[4] => 0.000819

[5] => 0.000540

[6] => 0.000150

[7] => 0.000251

[8] => 0.000556

[9] => 0.000321

[10] => 0.000595

[11] => 0.000410

[12] => 0.000144

[13] => 0.000113

[14] => 0.000294

[15] => 0.000355

[16] => 0.000430

[17] => 0.000220

[18] => 0.000127

[19] => 0.000082

[20] => 0.000117

[21] => 0.000182

[22] => 0.000115

[23] => 0.000116

[24] => 0.000159

[25] => 0.000157

[26] => 0.000162

[27] => 0.000114

[28] => 0.000117

[29] => 0.000079

[30] => 0.000115

[31] => 0.000161

[32] => 0.000108

[33] => 0.000113

[34] => 0.000078

[35] => 0.000106

[36] => 0.000265

[37] => 0.000248

[38] => 0.000154

[39] => 0.000104

[40] => 0.000142

[41] => 0.000181

[42] => 0.000122

[43] => 0.000117

[44] => 0.000083

[45] => 0.000103

[46] => 0.000164

[47] => 0.000109

[48] => 0.001698

[49] => 0.012823

[50] => 0.000296

[51] => 0.000792

[52] => 0.000140

[53] => 0.000213

[54] => 0.000114

[55] => 0.000152

[56] => 0.000723

[57] => 0.000993

[58] => 0.000109

[59] => 0.000188

 

If I select a category from Index.php

QUERY DEBUG:

 

Array

(

[QUERIES] => Array

(

[0] => select tax_description from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '0') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '0') and tr.tax_class_id = '0' order by tr.tax_priority

[1] => select value from sessions where sesskey = 'a3a0d3985787720c3edf9bf93f2bd0fe' and expiry > '1262733471'

[2] => select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies

[3] => delete from whos_online where time_last_click < '1262732571'

[4] => select count(*) as count from whos_online where session_id = 'a3a0d3985787720c3edf9bf93f2bd0fe'

[5] => update whos_online set customer_id = '0', full_name = 'Guest', ip_address = '67.71.41.199', time_last_click = '1262733471', last_page_url = '/store/index.php/subaru-c-22' where session_id = 'a3a0d3985787720c3edf9bf93f2bd0fe'

[6] => select banners_id, date_scheduled from banners where date_scheduled != ''

[7] => select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id

[8] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0

[9] => select categories_name from categories_description where categories_id = '22' and language_id = '1'

[10] => select count(*) as total from products_to_categories where categories_id = '22'

[11] => select count(*) as total from categories where parent_id = '22'

[12] => select products_id, products_image, products_tax_class_id, products_price from products where products_status = '1' order by products_date_added desc limit 10

[13] => select products_name from products_description where products_id = '1037' and language_id = '1'

[14] => select products_price, products_model from products where products_id = '1037'

[15] => select specials_new_products_price from specials where products_id = '1037' and status

[16] => select categories_id from products_to_categories where products_id = '1037'

[17] => select sale_specials_condition, sale_deduction_value, sale_deduction_type from salemaker_sales where sale_categories_all like '%,173,%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0000-00-00') and (sale_date_end >= now() or sale_date_end = '0000-00-00') and (sale_pricerange_from <= '264.9900' or sale_pricerange_from = '0') and (sale_pricerange_to >= '264.9900' or sale_pricerange_to = '0')

[18] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '38') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '74') and tr.tax_class_id = '5' group by tr.tax_priority

[19] => select cd.categories_name, c.categories_image from categories c, categories_description cd where c.categories_id = '22' and cd.categories_id = '22' and cd.language_id = '1'

[20] => select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from categories c, categories_description cd where c.parent_id = '22' and c.categories_id = cd.categories_id and cd.language_id = '1' order by sort_order, cd.categories_name

[21] => select parent_id from categories where categories_id = '22'

[22] => select parent_id from categories where categories_id = '235'

[23] => select parent_id from categories where categories_id = '22'

[24] => select parent_id from categories where categories_id = '34'

[25] => select parent_id from categories where categories_id = '22'

[26] => select parent_id from categories where categories_id = '23'

[27] => select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_price from products p, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '22' and p.products_status = '1' order by p.products_date_added desc limit 6

[28] => select distinct p.products_id, pd.products_name from products p, products_description pd, products_to_categories p2c, categories c where p.products_status = '1' and p.products_ordered > 0 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 '22' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10

[29] => select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '1' and s.status = '1' order by s.specials_date_added desc limit 10

[30] => select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from reviews r, reviews_description rd, products p, products_description pd where p.products_status = '1' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and rd.languages_id = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by r.reviews_id desc limit 10

[31] => select substring(reviews_text, 1, 60) as reviews_text from reviews_description where reviews_id = '3' and languages_id = '1'

[32] => select startdate, counter from counter

[33] => update counter set counter = '668048'

[34] => select banners_id, banners_title, banners_image, banners_html_text from banners where status = '1' and banners_group = '468x50'

[35] => select count(*) as count from banners_history where banners_id = '1' and date_format(banners_history_date, '%Y%m%d') = date_format(now(), '%Y%m%d')

[36] => update banners_history set banners_shown = banners_shown + 1 where banners_id = '1' and date_format(banners_history_date, '%Y%m%d') = date_format(now(), '%Y%m%d')

[37] => select count(*) as total from sessions where sesskey = 'a3a0d3985787720c3edf9bf93f2bd0fe'

[38] => update sessions set expiry = '1262734911', value = 'cart|O:12:\"shoppingCart\":6:{s:8:\"contents\";a:0:{}s:5:\"total\";i:0;s:6:\"weight\";i:0;s:6:\"cartID\";N;s:12:\"content_type\";b:0;s:9:\"shiptotal\";s:0:\"\";}language|s:7:\"english\";languages_id|s:1:\"1\";currency|s:3:\"CAD\";navigation|O:17:\"navigationHistory\":2:{s:4:\"path\";a:1:{i:0;a:4:{s:4:\"page\";s:9:\"index.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:1:{s:5:\"cPath\";s:2:\"22\";}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:0:{}}' where sesskey = 'a3a0d3985787720c3edf9bf93f2bd0fe'

)

 

[TIME] => Array

(

[0] => 0.000849

[1] => 0.000444

[2] => 0.000299

[3] => 0.000831

[4] => 0.000569

[5] => 0.003495

[6] => 0.000406

[7] => 0.000642

[8] => 0.000288

[9] => 0.000304

[10] => 0.001833

[11] => 0.000323

[12] => 0.000732

[13] => 0.000261

[14] => 0.000185

[15] => 0.000103

[16] => 0.000223

[17] => 0.000318

[18] => 0.000425

[19] => 0.000250

[20] => 0.000443

[21] => 0.000143

[22] => 0.000104

[23] => 0.000274

[24] => 0.000113

[25] => 0.001911

[26] => 0.000193

[27] => 0.020255

[28] => 0.021694

[29] => 0.020626

[30] => 0.000925

[31] => 0.000169

[32] => 0.000403

[33] => 0.000159

[34] => 0.000214

[35] => 0.000805

[36] => 0.001067

[37] => 0.000165

[38] => 0.000302

Share this post


Link to post
Share on other sites

Hello

I have a lot of contribution installed and a store with 600 categories and 15 000 products

Page load is very slow thanks to SEO-G contribution. Unfortunately I cannot remove it because I'll lost my google rankig

the other problem query is

[58] => select distinct p.products_id, p.products_image, p.products_price, p.products_tax_class_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 = '4' order by p.products_ordered desc, pd.products_name limit 10

[58] => 1.748247

products table already has indexes. Can this be further optimized ?

Also if I put mysql database server only on dedicated server would it help (to have 2 server -application and databse)

 

The other is

[663] => 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 = '4' and c.parent_id = '0' order by c.sort_order, cd.categories_name

I have about 300 queries of this type only categories id is changed

 

Can You give me advice

Thank You very much for your attention

Share this post


Link to post
Share on other sites

Can this be further optimized ?

I recall either a contribution or a thread in Tips & Tricks about caching this information.

The other is

[663] => 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 = '4' and c.parent_id = '0' order by c.sort_order, cd.categories_name

I have about 300 queries of this type only categories id is changed

Optimize categories box should help there.

Share this post


Link to post
Share on other sites

Hello again

I have isntall Optimize categories box, but its still there is a lot of queries

[385] => 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 = '4' and c.parent_id = '0' order by c.sort_order, cd.categories_name

....

[882] => 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 = '4' and c.parent_id = '307' order by c.sort_order, cd.categories_name

 

meanwhile i have noticed that animated box contribution http://addons.oscommerce.com/info/6246 makes problems too. In includes/boxes/specials.php of contribution in $rand_prod_query there was no limit and currently I have 650 specials ...

when I limited to 10 from 3500 queries in index currently I'm having 950

 

The other strange is

[175] => select seo_url_get from seo_url where seo_url_org = 'http://xxxx.xxx/product_info.php?products_id=16608'

[176] => select seo_url_get from seo_url where seo_url_org = 'http://xxxx.xxx/product_info.php?products_id=16608'

[177] => select seo_url_get from seo_url where seo_url_org = 'http://xxxx.xxx/product_info.php?products_id=16608'

its for every special product both with original boxes/specials.php and animated one

Share this post


Link to post
Share on other sites

I have isntall Optimize categories box, but its still there is a lot of queries

The contribution is for the standard categories box. If you have installed a contribution it might use its own code and then this contribution is not helpful (unless the menu is rewritten to make use of it).

meanwhile i have noticed that animated box contribution http://addons.oscommerce.com/info/6246 makes problems too. In includes/boxes/specials.php of contribution in $rand_prod_query there was no limit and currently I have 650 specials ...

when I limited to 10 from 3500 queries in index currently I'm having 950

 

The other strange is

[175] => select seo_url_get from seo_url where seo_url_org = 'http://xxxx.xxx/product_info.php?products_id=16608'

[176] => select seo_url_get from seo_url where seo_url_org = 'http://xxxx.xxx/product_info.php?products_id=16608'

[177] => select seo_url_get from seo_url where seo_url_org = 'http://xxxx.xxx/product_info.php?products_id=16608'

its for every special product both with original boxes/specials.php and animated one

Not strange really. A. There is indeed no limit to the number of products it selects and since it does a new query for every product to the table specials to see if it is a special where you just selected all specials :'( you get 1 query extra for each special.

 

The query for the seo_url is probably invoked from tep_href_link. I assume this adaptation to the specials box for that contribution will limit it to 1 for each products_id (storing it in $link):


$rand_prod_query = tep_db_query("select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added desc limit 10");?>

<tr>
<td>
   <?php
$specials_contents = '<table width="100%">
							<tr>
								<td align= "center">' . 
									BOX_HEADING_SPECIALS . '
								</td>
							</tr>
							<tr>
							<td>
								<div id="SPECContainer" class="speccontainer">';

/* cycle through array of products pulled from database*/
while($specials = tep_db_fetch_array($rand_prod_query)){
/* If the product is on special offer diplay the new price*/
		$products_price = '<s>'. $currencies->display_price($specials['products_price'], tep_get_tax_rate($specials['products_tax_clas_id'])) .'</s><br><span class="productSpecialPrice">' . $currencies->display_price($specials['specials_new_products_price'], tep_get_tax_rate($specials['products_tax_clas_id'])) .'</span>';

	$link = tep_href_link(FILENAME_PRODUCT_INFO,'products_id=' . $specials['products_id']);
	$specials_contents .='<div class="specials">
								<table>
									<tr>
										<td>			
										<a href="' . $link .'">' . $specials['products_name'] . '</a>
										</td>
									</tr>
									<tr>
										<td>			
										<a href="' . $link .'">' . tep_image(DIR_WS_IMAGES . $specials['products_image'], $specials['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>
										</td>
									</tr>
									<tr>
										<td>			
										<a href="' . $link .'">' . $products_price . '</a>
										</td>
									</tr>
								</table>
							</div>';
}
$specials_contents .='</div></td></tr></table>';
echo $specials_contents;?>

Share this post


Link to post
Share on other sites

Jan Zonjee

Its a standard category box but I have STS

Is it possible to disable (comment, remove) code for category box, I'm not planing to use it any way

I remove it from STS template, but queries still stays (its a normal I think, STS queries everytihng and displays only what is told to display)

I'll check installation again

 

I'll check link generation

Share this post


Link to post
Share on other sites

Its a standard category box but I have STS

I never used STS but I recall there is (in at least some versions of STS) a call to generate the drop-down menu that you also see in admin/categories.php in the admin (top right corner).

If you don't use the dropdown menu (I think it still generates all the queries even if you didn't show it) you are better off looking for that code and remove the call to it.

 

If you do use the dropdown menu you should look at this contribution tep_get_category_tree optimization which normally is only useful in the admin section around 2/3 from the top):

   echo tep_draw_form('goto', FILENAME_CATEGORIES, '', 'get');
   echo HEADING_TITLE_GOTO . ' ' . tep_draw_pull_down_menu('cPath', tep_get_category_tree(), $current_category_id, 'onChange="this.form.submit();"');
   echo '</form>';

Share this post


Link to post
Share on other sites

YES

sts is to blame

in catalog/includes/modules/sts_inc

/* $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');

*/

After I comment it queries disappear

and probably here is the key why I have no effect after installing your contribution

in same file its calling

function tep_get_category_tree

I think its bypass your code (not sure on this)

Share this post


Link to post
Share on other sites

Jan Zonjee

seo_url queries are still 3 times per one special product. I try your code buts its the same. I catch the idea and will try to find where they come from.

 

Current Parse Time: 3.697 s with 357 queries current result, starting from 3200 queries

 

 

[205] => select count(p.products_id) as total from products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = '1' and p.products_date_added > '1970-01-01' and p.products_id = pd.products_id and pd.language_id = '4'

Any ideas about this one

Thank You very much for saving my business :rolleyes:

Share this post


Link to post
Share on other sites

seo_url queries are still 3 times per one special product. I try your code buts its the same. I catch the idea and will try to find where they come from.

Perhaps you also added the whats_new.php from the Animated boxes addon? It has similar code (3 tep_href_links).

 

[205] => select count(p.products_id) as total from products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = '1' and p.products_date_added > '1970-01-01' and p.products_id = pd.products_id and pd.language_id = '4'

Any ideas about this one

This is not standard osC if you ask me. If I search a pretty standard osC catalog folder for p.products_date_added there are only four files:

advanced_search_results.php, products_new.php, includes/modules/new_products.php and product_info.php. Since it has a count query I would look at modules/new_products.php first. The count query should be in there only once though.

 

I don't know if you added the tax class of Chemo already but if not he wrote a more simpler alternative later:

http://forums.osquantum.org/index.php?showtopic=34

 function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1) {
   static $tax_rates = array();

   global $customer_zone_id, $customer_country_id;

   if ( ($country_id == -1) && ($zone_id == -1) ) {
     if (!tep_session_is_registered('customer_id')) {
       $country_id = STORE_COUNTRY;
       $zone_id = STORE_ZONE;
     } else {
       $country_id = $customer_country_id;
       $zone_id = $customer_zone_id;
     }
   }

   if ( isset($tax_rates[$class_id][$country_id][$zone_id]['rate']) ){
       return $tax_rates[$class_id][$country_id][$zone_id]['rate'];
   }


   $tax_query = tep_db_query("select sum(tax_rate) as tax_rate from " . TABLE_TAX_RATES . " tr left join " . TABLE_ZONES_TO_GEO_ZONES . " za on (tr.tax_zone_id = za.geo_zone_id) left join " . TABLE_GEO_ZONES . " tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '" . (int)$country_id . "') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '" . (int)$zone_id . "') and tr.tax_class_id = '" . (int)$class_id . "' group by tr.tax_priority");
   if (tep_db_num_rows($tax_query)) {
     $tax_multiplier = 1.0;
     while ($tax = tep_db_fetch_array($tax_query)) {
       $tax_multiplier *= 1.0 + ($tax['tax_rate'] / 100);
     }
     $tax_rates[$class_id][$country_id][$zone_id]['rate'] = ($tax_multiplier - 1.0) * 100;
     return ($tax_multiplier - 1.0) * 100;
   } else {
     $tax_rates[$class_id][$country_id][$zone_id]['rate'] = 0;
     return 0;
   }
   return $tax_rates[$class_id][$country_id][$zone_id]['rate'];
 }

357 Queries is still quite a bit...

Share this post


Link to post
Share on other sites

Perhaps you also added the whats_new.php from the Animated boxes addon? It has similar code (3 tep_href_links).

 

 

This is not standard osC if you ask me. If I search a pretty standard osC catalog folder for p.products_date_added there are only four files:

advanced_search_results.php, products_new.php, includes/modules/new_products.php and product_info.php. Since it has a count query I would look at modules/new_products.php first. The count query should be in there only once though.

 

I don't know if you added the tax class of Chemo already but if not he wrote a more simpler alternative later:

 

357 Queries is still quite a bit...

 

Yes I have added tax class also page cache class, I try to cache seo-g queries, but didn't succeed

Looks like this query is from Product Listing Enhancement by Mr. Sam (Spooks)

 

And what is your advice - is it better to have dedicated database server only or to share it with web server

I'm hosting my store at home on my own server and I can reconfigure system with minimum downtime

Share this post


Link to post
Share on other sites

Yes I have added tax class also page cache class,

If that page cache contribution is from Chemo I can tell he later found it a bad idea.

 

I don't know how the SEO contribution of yours works (how the url looks) but from what I have seen from the ULTIMATE Seo Urls 5 contribution is that it is pretty flexible in the way you want to shape the url. Most likely it is less heavy on the database.

 

And what is your advice - is it better to have dedicated database server only or to share it with web server

I'm hosting my store at home on my own server and I can reconfigure system with minimum downtime

Sorry, but that is beyond my expertise. I do have the gut feeling that with a dedicated server (depends on the amount of traffic on the server of course) the amount of categories and products is not unreasonably high.

 

Still, I'm certain that with rewriting a number of things in osC you can lessen the strain on the database and speed up the shop.

With a lot of products the inclusion of the table products description (to get the name of a product e.g. in the product listing) the query is slow. However, leaving that table out (even leaving the table specials out), retrieving the product_id's and than query for special prices and the name speeds up the code despite it now being three queries.

Share this post


Link to post
Share on other sites

If that page cache contribution is from Chemo I can tell he later found it a bad idea

 

My mistake, I mean advanced cache class trying to cache seo-g queries in product listing

I'm using SEO-G by Enigma and problem if I switch to another SEO url I'll lost my google rating, links from external site to mine

I have about 15 000 products, 1500-2500 visitors daily, about 800 categories (categories will grow twice next month)

Categories are quit permanent, for 2 years I have add not more ten

I'm thinking of removing Category Infobox and replaced with single level category different on every page

 

My test server is with Ultimate SEO URL and I'm impressed ... now I'm paying for mistake that I've made before 2 years with this SEO-G

 

Duplicated queries from seo_url are not from animated box contribution, I've tasted with default boxes, with animated boxes, with one box etc... result is always the same.

I'll continue tomorrow

 

About dedicated server - i'll try next week and will post result

Probably its more business decision - what is chipper hardware, my time, or hiring someone to do the job

Of course, its sure - whatever hardware You have it can be killed with a buggy query

Share this post


Link to post
Share on other sites

Given that it is in the flavour of this well read topic I thought I'd post about a new contribution you could well find very useful in debugging, optimising and keeping osCommerce free of errors.

 

This contribution is PHP5.2+ only PHP 4 is NOT supported.

 

KissER Error Handling & Debugging

 

I won't give a load of details here as it is all covered in the first post of that support topic.

 

Hope it helps.

Share this post


Link to post
Share on other sites

YES

sts is to blame

in catalog/includes/modules/sts_inc

/* $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');

*/

After I comment it queries disappear

and probably here is the key why I have no effect after installing your contribution

in same file its calling

function tep_get_category_tree

I think its bypass your code (not sure on this)

 

 

Unbelieveable!!! If people would only read. The STS User Manual clearly states where and how to get rid of unused code. It actaully recommends it. WOW!

STS comes with a lot of user tags created to assist those who do not understand PHP and cannot create the tags on their own. The manual states that if you do not want to use these tags, it would be better to remove them to save yourself some unnecessary queries. whistling.gif


Bill Kellum

 

Sounds Good Productions

STS Tutorials & more: STSv4.6, STS Add-ons (STS Power Pack), STS V4 Forum STS Forum FREE TEMPLATE

Share this post


Link to post
Share on other sites

I have just installed the configuration cache contrib.

I seem to be having an issue with one particular configuration that brings down admin and catalog.

Unless I delete the 5th line below within the cached file(cachefile.inc.php) nothing works.

define(MODULE_PAYMENT_INSTALLED,'paypal_standard.php'); 
define(MODULE_ORDER_TOTAL_INSTALLED,'ot_subtotal.php;ot_shipping.php;ot_total.php'); 
define(MODULE_SHIPPING_INSTALLED,'flat.php'); 
define(SEO_URLS_CHAR_CONVERT_SET,''); 
define(,''); 
define(SEO_URLS_ENABLED,'true'); 
define(SEO_URLS_ADD_CPATH_TO_PRODUCT_URLS,'false'); 
define(SEO_URLS_ADD_CAT_PARENT,'true'); 
define(SEO_URLS_FILTER_SHORT_WORDS,'2');

Does anyone have an idea what could be causing this.

It seems to hang around where the configs for Ulimate SEO urls 5.

I would be ok if the site was running as configs would hardly be changed at all but the store is in development and its a bit a a pain in the butt.

Share this post


Link to post
Share on other sites

If that page cache contribution is from Chemo I can tell he later found it a bad idea.

I must have missed this on the board :S

 

What do you recommend for caching instead, Jan?

 

 

I already have your optimize categories add-on, but the product pages still need caching.

Share this post


Link to post
Share on other sites

Hello,

 

I've read through all the pages of this fabalous thread and tried to implement some of the suggestions, but I have some queries, where I don't know what to do.

 

I have implemented as far:

- disabled category count in admin

- disabled counter in footer

- optimize tep_get_tax() method

- optimized categories box v1.2

- set indexes on specials like Monika mentioned here

 

but I'm still at 2.239 seconds page load time with 314 queries.

 

The query debug output showed me 3 queries, which have a loading time far beyond:

[65] => 0.219468
[141] => 0.225571
[217] => 0.226178

[65] => select c.categories_id, cd.categories_name from categories c, categories_description cd where parent_id = '290' and c.categories_id = cd.categories_id and cd.language_id = '2' order by sort_order, cd.categories_name
[141] => select c.categories_id, cd.categories_name from categories c, categories_description cd where parent_id = '272' and c.categories_id = cd.categories_id and cd.language_id = '2' order by sort_order, cd.categories_name
[217] => select c.categories_id, cd.categories_name from categories c, categories_description cd where parent_id = '231' and c.categories_id = cd.categories_id and cd.language_id = '2' order by sort_order, cd.categories_name

 

And I don't know exactly, where these queries are coming from or why they need so long to run.

My guess is, that they come from my categories box, which is "enhanced" with the contribution Category box enhancement v1.1.

 

The mentioned 314 queries are only correct, when I have the dropdown menu of all categories disabled in the Enhanced cat box. Otherwise I have something around 520 queries! *ouch*

 

But I also tried this with the standard categories box from RC2a with no better performance.

 

While reading through the 40 pages of this posting, I honestly thought, that the cat box is my biggest problem (I have 203 categories in total).

And with implementing the a.m. improvements, I had the hope of getting rid of some of my queries.

 

From the 314 queries in total, xx of them are:

68 queries are coming from "select code from languages where languages_id = '2'"

203-205 queries are coming from "select c.categories..... where parent_id = 'xx'

 

 

Can someone of you give me some more hints, where to continue?

 

Thanks in advance,

Regards

Andreas

Share this post


Link to post
Share on other sites

Sorry, I was wrong! Not any of the left or right boxes are the problem, the index.php itself causing me that trouble.

Even with the RC2a version of this file, I have over 200 queries.

 

Any help highly appreciated!

Share this post


Link to post
Share on other sites

Sorry, I was wrong! Not any of the left or right boxes are the problem, the index.php itself causing me that trouble.

Even with the RC2a version of this file, I have over 200 queries.

 

How are we supposed to come up with suggestions if you say one thing and then say the next?

 

If you didn't mentioned this I would have thought that the enhanced categories box was the problem and that surely the object CountProductsStore could be used in some way or another to avoid those large number of queries.

 

Why those three particular queries should take longer than others is not logical. Could be a coincidence. That is why you always have to check those queries a number of times and then compare the results. One single output might be misleading.

 

The query select code from languages where languages_id = '2' is not used in standard osC so hard to say how to avoid that. Shouldn't be that hard to store that in some way or another to avoid 67 queries for the same thing though once you know where it comes from.

Share this post


Link to post
Share on other sites

How are we supposed to come up with suggestions if you say one thing and then say the next?

 

If you didn't mentioned this I would have thought that the enhanced categories box was the problem and that surely the object CountProductsStore could be used in some way or another to avoid those large number of queries.

Jan, you're right, I'm sorry.

 

Ok, how should I explain?

Yesterday, I installed some optimizations as mentioned above, also your optimized categories box v1.2.

With that, I hoped, I could be able to get rid of some of the queries.

The result was not as overwhelming as expected, I could only get rid of some queries.

 

Later I kicked out all boxes in the column_left/right and also commented out all modules in the index.php like new_products etc.

In the end, I had a nearly empty index.php (no content), but my queries were still at a total of 260 queries.

This result brought me to the statement of my last posting, that the index.php is my problem.

 

Even later, I replaced my index.php with the vanilla-RC2a-version of the index.php (only replaced that file), and the result was nearly the same.

I also tried the vanilla-version of the categories box (catalog/includes/boxes/categories.php), with also no better success.

 

Why those three particular queries should take longer than others is not logical. Could be a coincidence. That is why you always have to check those queries a number of times and then compare the results. One single output might be misleading.

Yes that right too. Today it was only one, but another then the mentioned 3 queries. Today it was a specials-query with a "order by rand()" in it, but step-by-step.

 

The query select code from languages where languages_id = '2' is not used in standard osC so hard to say how to avoid that. Shouldn't be that hard to store that in some way or another to avoid 67 queries for the same thing though once you know where it comes from.

I found that right now. That "select code from language..." comes completely from my seo.class.php.

I set UltimateSEO to inactive and my queries dropped from 314 (all content activated again) to 235.

 

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?

Share this post


Link to post
Share on other sites

×