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
Quote
Current Parse Time: 2.363 s with 1014 queries
Product Info pages I am getting the below
Quote
Current Parse Time: 1.009 s with 55 queries
Index.php
Quote
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
Quote
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
0
FWR Media, on 22 November 2009, 14:19, said:
