jbasszd Posted December 29, 2007 Share Posted December 29, 2007 Nope I'm not playing any more .. read this thread and come back again when you have found and solved the "count problem" Woo there i read page after page if this, i took out the counter at the bottom, there is no count on the products and cats, but the are the for the news desk, and i understand that they are for the number viewed counts at the top... if i uninstall newsdesk then i take all that away... If you run newsdesk as well then please let me know... if i'm grossly missing the point then sorry but after read thread after thread for like 30 diffrenet reasons it's bound to happen... :) Link to comment Share on other sites More sharing options...
jbasszd Posted December 29, 2007 Share Posted December 29, 2007 oh by the way i am very happy at the fact you took the time to write... :) Link to comment Share on other sites More sharing options...
jbasszd Posted December 29, 2007 Share Posted December 29, 2007 ok so i sreached the thread for count... well all the way back to Posted on: Mar 14 2005, 11:55 AM post but a few more posts... all the count stuff was about the [13] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id="1" order by sort_order, cd.categories_name which did get fixed by the recamended solutions... but [27] => select count(*) as count from newsdesk_categories where parent_id = '10' a newsdesk query and [25] => select c.categories_id, cd.categories_name, c.parent_id from newsdesk_categories c, newsdesk_categories_description cd where c.catagory_status = '1' and c.parent_id = '' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name also a newsdesk query, are the time hogs... if you are saying that the contribs i used to fix the suggested areas, categories, products, will work on the news desk, then do i just have to change something? doesn't seem that strigt forward... sorry if it seems i'm being dumb but we can't be experts at it all... thank you all again... Link to comment Share on other sites More sharing options...
peteschulte Posted January 9, 2008 Share Posted January 9, 2008 Hello, I have been working on optimizing the MS2 codebae for several months and am going to do a test site from scratch so everyone can follow along. . . . Vanilla Install Stats: Parse Time: .328 seconds Queries - 92 So, save 40 queries right off the bat for disabling category counts. More optimizations tomorrow...whe I start to dissect the code and get this puppy screaming. This looks like a valuable thread! What does "vanilla" mean in this context, please? Pete Schulte Link to comment Share on other sites More sharing options...
Jan Zonjee Posted January 9, 2008 Share Posted January 9, 2008 This looks like a valuable thread! What does "vanilla" mean in this context, please? Right after install, so no contributions added. Link to comment Share on other sites More sharing options...
♥stubbsy Posted January 31, 2008 Share Posted January 31, 2008 Not really advice or ideas, but you can add Chemo's debug contribution to the admin relatively easy (the install instructions only mention the catalog side, but it is not hard to imagine how to add it to the admin side). In that case you can at least find out what is/are the slow query/queries. Hi there, A little while ago I asked for advice about speeding up the admin side of my site, esp the products attributes page and Janz suggested that I install the debug in the admin... well some time later and after the birth of my little boy, i've finally had chance to do so... My products_attributes page, which is 'out of the box' (i'm pretty sure), takes over 30 seconds to load, but with not many queries, i wondered if anyone had any ideas? Here are the results.... Current Parse Time: 43.201 s with 38 queries QUERY DEBUG: Array ( [QUERIES] => Array ( [0] => select configuration_key as cfgKey, configuration_value as cfgValue from configuration [1] => select value from sessions where sesskey = '7cbb6abe00b2f829909515722afa026a' and expiry > '1201816629' [2] => select languages_id, name, code, image, directory from languages order by sort_order [3] => select * from products_options where language_id = '1' order by products_options_id [4] => select * from products_options where language_id = '1' order by products_options_id LIMIT 0, 10 [5] => select max(products_options_id) + 1 as next_id from products_options [6] => select max(products_options_id) + 1 as next_id from products_options [7] => select max(products_options_id) + 1 as next_id from products_options [8] => select max(products_options_id) + 1 as next_id from products_options [9] => select max(products_options_id) + 1 as next_id from products_options [10] => select max(products_options_id) + 1 as next_id from products_options [11] => select max(products_options_id) + 1 as next_id from products_options [12] => select max(products_options_id) + 1 as next_id from products_options [13] => select max(products_options_id) + 1 as next_id from products_options [14] => select max(products_options_id) + 1 as next_id from products_options [15] => select pov.products_options_values_id, pov.products_options_values_name, pov2po.products_options_id from products_options_values pov left join products_options_values_to_products_options pov2po on pov.products_options_values_id = pov2po.products_options_values_id where pov.language_id = '1' order by pov.products_options_values_id [16] => select pov.products_options_values_id, pov.products_options_values_name, pov2po.products_options_id from products_options_values pov left join products_options_values_to_products_options pov2po on pov.products_options_values_id = pov2po.products_options_values_id where pov.language_id = '1' order by pov.products_options_values_id LIMIT 0, 10 [17] => select products_options_name from products_options where products_options_id = '0' and language_id = '1' [18] => select max(products_options_values_id) + 1 as next_id from products_options_values [19] => select products_options_name from products_options where products_options_id = '14' and language_id = '1' [20] => select max(products_options_values_id) + 1 as next_id from products_options_values [21] => select products_options_name from products_options where products_options_id = '14' and language_id = '1' [22] => select max(products_options_values_id) + 1 as next_id from products_options_values [23] => select products_options_name from products_options where products_options_id = '14' and language_id = '1' [24] => select max(products_options_values_id) + 1 as next_id from products_options_values [25] => select products_options_name from products_options where products_options_id = '14' and language_id = '1' [26] => select max(products_options_values_id) + 1 as next_id from products_options_values [27] => select products_options_name from products_options where products_options_id = '14' and language_id = '1' [28] => select max(products_options_values_id) + 1 as next_id from products_options_values [29] => select products_options_name from products_options where products_options_id = '14' and language_id = '1' [30] => select max(products_options_values_id) + 1 as next_id from products_options_values [31] => select products_options_name from products_options where products_options_id = '14' and language_id = '1' [32] => select max(products_options_values_id) + 1 as next_id from products_options_values [33] => select products_options_name from products_options where products_options_id = '14' and language_id = '1' [34] => select max(products_options_values_id) + 1 as next_id from products_options_values [35] => select products_options_name from products_options where products_options_id = '14' and language_id = '1' [36] => select max(products_options_values_id) + 1 as next_id from products_options_values [37] => select products_options_id, products_options_name from products_options where language_id = '1' order by products_options_name ) [TIME] => Array ( [0] => 0.002552 [1] => 0.000223 [2] => 0.000168 [3] => 0.001843 [4] => 0.000452 [5] => 0.000164 [6] => 0.000138 [7] => 0.000147 [8] => 0.000140 [9] => 0.000179 [10] => 0.000141 [11] => 0.000136 [12] => 0.000135 [13] => 0.000133 [14] => 0.000142 [15] => 27.354584 [16] => 15.493826 [17] => 0.000282 [18] => 0.000153 [19] => 0.000186 [20] => 0.000133 [21] => 0.000165 [22] => 0.000129 [23] => 0.000158 [24] => 0.000139 [25] => 0.000161 [26] => 0.000133 [27] => 0.000161 [28] => 0.000129 [29] => 0.000160 [30] => 0.000134 [31] => 0.000158 [32] => 0.000127 [33] => 0.000162 [34] => 0.000131 [35] => 0.000161 [36] => 0.000130 [37] => 0.001399 ) ) -------------------------------------------------------------------------------- SESSION: Array ( [language] => english [languages_id] => 1 [selected_box] => catalog [output] => 1 ) -------------------------------------------------------------------------------- COOKIE: Array ( [osCAdminID] => 7cbb6abe00b2f829909515722afa026a [__utma] => 44521137.739245950.1200437002.1201338102.1201817264.6 [__utmz] => 44521137.1200437002.1.1.utmccn=(direct)|utmcsr=(direct)|utmcmd=(none) [osCsid] => 45b2ff7b7d985927d971b2e0e2723855 [__utmb] => 44521137 [__utmc] => 44521137 ) POST: Array ( ) -------------------------------------------------------------------------------- GET: Array ( [output] => 1 ) Parse Time: 43.212s Any suggestions/ideas Thanks Dave Link to comment Share on other sites More sharing options...
Jan Zonjee Posted January 31, 2008 Share Posted January 31, 2008 My products_attributes page, which is 'out of the box' (i'm pretty sure), takes over 30 seconds to load, but with not many queries, i wondered if anyone had any ideas? Here are the results.... Well, at least it is obvious where the bad guys are: [15] => select pov.products_options_values_id, pov.products_options_values_name, pov2po.products_options_id from products_options_values pov left join products_options_values_to_products_options pov2po on pov.products_options_values_id = pov2po.products_options_values_id where pov.language_id = '1' order by pov.products_options_values_id [16] => select pov.products_options_values_id, pov.products_options_values_name, pov2po.products_options_id from products_options_values pov left join products_options_values_to_products_options pov2po on pov.products_options_values_id = pov2po.products_options_values_id where pov.language_id = '1' order by pov.products_options_values_id LIMIT 0, 10 [15] => 27.354584 [16] => 15.493826 In RC2 a number of indexes to tables were added. I notice that the products_options_values_id is not a key in the table products_options_values_to_products_options. Perhaps adding an index to that table might help that query along (I hope it is the correct syntax). alter table products_options_values_to_products_options add index idx_products_options_values_id (product_options_values_id); Link to comment Share on other sites More sharing options...
♥FWR Media Posted January 31, 2008 Share Posted January 31, 2008 well some time later and after the birth of my little boy Firstly CONGRATULATIONS! Secondly .. Queries 15 and 16 are site killingly bad. (ofc killingly is a word! .. well you knew what I meant :D ) [15] => 27.354584 [16] => 15.493826 Run the following in SQL and report back the difference: - ALTER TABLE `products_options_values_to_products_options` ADD INDEX ( `products_options_values_id` ) Ultimate SEO Urls 5 PRO - Multi Language Modern, Powerful SEO Urls KissMT Dynamic SEO Meta & Canonical Header Tags KissER Error Handling and Debugging KissIT Image Thumbnailer Security Pro - Querystring protection against hackers ( a KISS contribution ) If you found my post useful please click the "Like This" button to the right. Please only PM me for paid work. Link to comment Share on other sites More sharing options...
Jan Zonjee Posted January 31, 2008 Share Posted January 31, 2008 Firstly CONGRATULATIONS! Oops, I second that. Link to comment Share on other sites More sharing options...
♥stubbsy Posted February 1, 2008 Share Posted February 1, 2008 thanks for the quick reply, I'll give it a go this evening and let you know how I get on Cheers Dave Link to comment Share on other sites More sharing options...
♥stubbsy Posted February 2, 2008 Share Posted February 2, 2008 Gents, Once again I'm amazed by the power of indexes! The page now loads instantly :) Thanks again for your help... Now that I have the queries debug installed in the admin section this opens up a whole new area to explore :) One last thing, you might consider this off topic as I think it is to do with attribute sets plus (but I'm not sure) so feel free to tell me if it is. my product_info page takes about 5 seconds to load on one site and I'm pretty sure its to do with that contribution but have been unable to resolve it. I can see there are 4 or 5 queries causing the delay but can you see any obvious rememdy? QUERY DEBUG: Array ( [QUERIES] => Array ( [0] => select configuration_key as cfgKey, configuration_value as cfgValue from configuration [1] => select value from sessions where sesskey = 'cd4458c2ce35b4b6ccebabd6acb6b401' and expiry > '1201943998' [2] => select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies [3] => DELETE FROM cache WHERE cache_expires <= '2008-02-02 09:19:58' [4] => delete from whos_online where time_last_click < '1201943098' [5] => select count(*) as count from whos_online where session_id = 'cd4458c2ce35b4b6ccebabd6acb6b401' [6] => update whos_online set customer_id = '0', full_name = 'Guest', ip_address = '81.86.64.126', time_last_click = '1201943998', last_page_url = '/sxlites-series-kawasaki-backgrounds-p-128.html?output=1' where session_id = 'cd4458c2ce35b4b6ccebabd6acb6b401' [7] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0 [8] => select featured_id from featured where status = '1' and now() >= expires_date and expires_date > 0 [9] => select p2c.categories_id from products p, products_to_categories p2c where p.products_id = '128' and p.products_status = '1' and p.products_id = p2c.products_id limit 1 [10] => select parent_id from categories where categories_id = '104' [11] => select parent_id from categories where categories_id = '94' [12] => select categories_name from categories_description where categories_id = '94' and language_id = '1' [13] => select categories_name from categories_description where categories_id = '104' and language_id = '1' [14] => select products_name from products_description where products_id = '128' [15] => select count(*) as total from products p, products_description pd where p.products_status = '1' and p.products_id = '128' and pd.products_id = p.products_id and pd.language_id = '1' [16] => select pd.language_id, p.products_id, pd.products_name, pd.products_description, pd.products_head_title_tag, pd.products_head_keywords_tag, pd.products_head_desc_tag, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from products p, products_description pd where p.products_id = '128' and pd.products_id = '128' and pd.language_id ='1' [17] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.categories_id = cd.categories_id and cd.language_id="1" AND (c.parent_id = "0" OR c.parent_id = "94" OR c.parent_id = "104")order by sort_order, cd.categories_name [18] => select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_weight, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from products p, products_description pd where p.products_status = '1' and p.products_id = '128' and pd.products_id = p.products_id and pd.language_id = '1' [19] => update products_description set products_viewed = products_viewed+1 where products_id = '128' and language_id = '1' [20] => select specials_new_products_price from specials where products_id = '128' and status [21] => 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 = '222') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '0') and tr.tax_class_id = '1' group by tr.tax_priority [22] => select count(*) as total from products_options popt, products_attributes patrib where patrib.products_id='128' and patrib.options_id = popt.products_options_id and popt.language_id = '1' [23] => select distinct popt.products_options_id, popt.products_options_name, popt.products_options_type, popt.products_options_length, popt.products_options_comment from products_options popt, products_attributes patrib where patrib.products_id='128' and patrib.options_id = popt.products_options_id and popt.language_id = '1' order by popt.products_options_name [24] => SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '128' AND pa.options_id = '3' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id [25] => SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '128' AND pa.options_id = '18' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id [26] => SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '128' AND pa.options_id = '13' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id [27] => SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '128' AND pa.options_id = '7' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id [28] => select distinct patrib.options_values_price, patrib.price_prefix from products_attributes patrib where patrib.products_id='128' and patrib.options_id = '19' [29] => select distinct patrib.options_values_price, patrib.price_prefix from products_attributes patrib where patrib.products_id='128' and patrib.options_id = '20' [30] => SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '128' AND pa.options_id = '16' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id [31] => SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '128' AND pa.options_id = '15' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id [32] => select distinct patrib.options_values_price, patrib.price_prefix from products_attributes patrib where patrib.products_id='128' and patrib.options_id = '2' [33] => SELECT pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix , pase.sort_order FROM products_attributes pa, products_attributes_sets_to_products pas2pa, products_attributes_sets pas, products_attributes_sets_elements pase, products_options_values pov WHERE pa.products_id = '128' AND pa.options_id = '17' AND pas2pa.products_id = pa.products_id AND pas.products_attributes_sets_id = pas2pa.products_attributes_sets_id AND pas.products_options_id = pa.options_id AND pase.products_attributes_sets_id = pas.products_attributes_sets_id AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id AND pov.language_id = '1' ORDER BY pase.sort_order, pa.options_values_id [34] => select count(*) as count from reviews where products_id = '128' [35] => select r.reviews_id, r.customers_name, r.date_added, rd.reviews_text, r.reviews_rating FROM reviews r, reviews_description rd WHERE r.reviews_id = rd.reviews_id AND r.products_id = '128' AND rd.languages_id = '1' and r.approved = '1' ORDER BY r.date_added DESC LIMIT 5 [36] => select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o, products p where opa.products_id = '128' and opa.orders_id = opb.orders_id and opb.products_id != '128' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit 6 [37] => select count(*) as total from sessions where sesskey = 'cd4458c2ce35b4b6ccebabd6acb6b401' [38] => update sessions set expiry = '1201945445', value = 'cart|O:12:\"shoppingcart\":5:{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;}language|s:7:\"english\";languages_id|s:1:\"1\";currency|s:3:\"GBP\";navigation|O:17:\"navigationhistory\":2:{s:4:\"path\";a:4:{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:{}}i:1;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:\"94\";}s:4:\"post\";a:0:{}}i:2;a:4:{s:4:\"page\";s:9:\"index.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:1:{s:5:\"cPath\";s:6:\"94_104\";}s:4:\"post\";a:0:{}}i:3;a:4:{s:4:\"page\";s:16:\"product_info.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:2:{s:11:\"products_id\";s:3:\"128\";s:6:\"output\";s:1:\"1\";}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:0:{}}referral_id|s:0:\"\";' where sesskey = 'cd4458c2ce35b4b6ccebabd6acb6b401' ) [TIME] => Array ( [0] => 0.002598 [1] => 0.000213 [2] => 0.000224 [3] => 0.000128 [4] => 0.000262 [5] => 0.000243 [6] => 0.000737 [7] => 0.000169 [8] => 0.000156 [9] => 0.000193 [10] => 0.000139 [11] => 0.000140 [12] => 0.000171 [13] => 0.000162 [14] => 0.000181 [15] => 0.000191 [16] => 0.000325 [17] => 0.000612 [18] => 0.000332 [19] => 0.000222 [20] => 0.000123 [21] => 0.000257 [22] => 0.000725 [23] => 0.001086 [24] => 0.703501 [25] => 0.812853 [26] => 0.796464 [27] => 1.248107 [28] => 0.003105 [29] => 0.002559 [30] => 1.620750 [31] => 1.035404 [32] => 0.001375 [33] => 0.834830 [34] => 0.000173 [35] => 0.000259 [36] => 0.000411 [37] => 0.000154 [38] => 0.000216 ) ) -------------------------------------------------------------------------------- SESSION: Array ( [cart] => shoppingcart Object ( [contents] => Array ( ) [total] => 0 [weight] => 0 [cartID] => [content_type] => ) [language] => english [languages_id] => 1 [currency] => GBP [navigation] => navigationhistory Object ( [path] => Array ( [0] => Array ( => index.php [mode] => NONSSL [get] => Array ( ) [post] => Array ( ) ) [1] => Array ( => index.php [mode] => NONSSL [get] => Array ( [cPath] => 94 ) [post] => Array ( ) ) [2] => Array ( => index.php [mode] => NONSSL [get] => Array ( [cPath] => 94_104 ) [post] => Array ( ) ) [3] => Array ( => product_info.php [mode] => NONSSL [get] => Array ( [products_id] => 128 [output] => 1 ) [post] => Array ( ) ) ) [snapshot] => Array ( ) ) [referral_id] => [output] => 1 ) -------------------------------------------------------------------------------- COOKIE: Array ( [osCsid] => cd4458c2ce35b4b6ccebabd6acb6b401 ) POST: Array ( ) -------------------------------------------------------------------------------- GET: Array ( [products_id] => 128 [output] => 1 ) Thanks again Dave Link to comment Share on other sites More sharing options...
Jan Zonjee Posted February 3, 2008 Share Posted February 3, 2008 my product_info page takes about 5 seconds to load on one site and I'm pretty sure its to do with that contribution but have been unable to resolve it. I can see there are 4 or 5 queries causing the delay but can you see any obvious remedy? I hoped Robert would answer that one but maybe he hasn't seen your post :) Not being a database expert I would try the following: - add an index on options_values_id in products_attributes and products_attributes_sets_elements because of AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id would probably benefit from that (products_options_values_id is already an index in products_options_values). - add an index on products_id in products_attributes_sets_to_products because of pas2pa.products_id = pa.products_id - add an index on products_id in products_attributes_sets_to_products because of AND pas2pa.products_id = pa.products_id In RC2 already an index on products_id was added in products_attributes: alter table products_attributes add index idx_products_attributes_products_id (products_id); Personally, I would also try to rewrite the code in products_info.php to first get all the options_id's for the particular products_id and then do them in one query (so instead of WHERE pa.products_id = '128' AND pa.options_id = '3' and then for options_id 18, 13, 7, 16, 15, and 17 do a query for WHERE pa.products_id = '128' AND pa.options_id IN (3, 18, 13, 7, 16, 15, 17) and save that in an array for when you need it). Then at least you only get hammered once with a slow query instead of 7 times for that particular product. Link to comment Share on other sites More sharing options...
ShoppingMantra Posted February 3, 2008 Share Posted February 3, 2008 Hi, I installed this contribution but getting this error: "Warning: error_log(/var/log/www/tep/page_parse_time.log) [function.error-log]: failed to open stream: No such file or directory in /home/shopping/public_html/admin/includes/classes/logger.php on line 49 Parse Time: 0.011s " What shall i do Link to comment Share on other sites More sharing options...
Jan Zonjee Posted February 3, 2008 Share Posted February 3, 2008 What shall i do What does PHP tell you: No such file or directory Link to comment Share on other sites More sharing options...
Guest Posted February 4, 2008 Share Posted February 4, 2008 Hello guys and gals, I have a situation. I am new to the osc community and I'm attemptin gto get my store up and running ASAP. My site is comehomefurnishings.com, I hired a guy from getafreelancer.com to install some contributions. He did an outsyanding job. However, now my site take the better part of 2 minutes to load. I installed the database optimizer no change. Any advice? Thanks Prof. X Link to comment Share on other sites More sharing options...
Guest Posted February 4, 2008 Share Posted February 4, 2008 Hello guys and gals, I have a situation. I am new to the osc community and I'm attemptin gto get my store up and running ASAP. My site is comehomefurnishings.com, I hired a guy from getafreelancer.com to install some contributions. He did an outsyanding job. However, now my site take the better part of 2 minutes to load. I installed the database optimizer no change. Any advice? Thanks Prof. X Nevermind guys, I recieved some ridiculous message about my original message. I deleted everything and I'll start over. Thanks for nothing! Link to comment Share on other sites More sharing options...
♥stubbsy Posted February 4, 2008 Share Posted February 4, 2008 I hoped Robert would answer that one but maybe he hasn't seen your post :) Not being a database expert I would try the following: - add an index on options_values_id in products_attributes and products_attributes_sets_elements because of AND pase.options_values_id = pa.options_values_id AND pov.products_options_values_id = pa.options_values_id would probably benefit from that (products_options_values_id is already an index in products_options_values). - add an index on products_id in products_attributes_sets_to_products because of pas2pa.products_id = pa.products_id - add an index on products_id in products_attributes_sets_to_products because of AND pas2pa.products_id = pa.products_id In RC2 already an index on products_id was added in products_attributes: alter table products_attributes add index idx_products_attributes_products_id (products_id); Personally, I would also try to rewrite the code in products_info.php to first get all the options_id's for the particular products_id and then do them in one query (so instead of WHERE pa.products_id = '128' AND pa.options_id = '3' and then for options_id 18, 13, 7, 16, 15, and 17 do a query for WHERE pa.products_id = '128' AND pa.options_id IN (3, 18, 13, 7, 16, 15, 17) and save that in an array for when you need it). Then at least you only get hammered once with a slow query instead of 7 times for that particular product. Hi Jan, Rob did pm me with a tip regarding what i could do with the results of the debug queries which I have been playing with. Thanks for your help also, i added the code to the database and again immediate results. I'll take a look at rewriting the coding to pull all the queries into one. I wish I knew a bit more about programming and databases to be able to look at the debug and the information that can provide and come up with solutions myself. Currently I can find the issues but am not competent enough to come up with the solutions. But I'm learning :) Thanks again Dave Link to comment Share on other sites More sharing options...
♥FWR Media Posted February 4, 2008 Share Posted February 4, 2008 Hi Jan, Rob did pm me with a tip regarding what i could do with the results of the debug queries which I have been playing with. Thanks for your help also, i added the code to the database and again immediate results. I'll take a look at rewriting the coding to pull all the queries into one. I wish I knew a bit more about programming and databases to be able to look at the debug and the information that can provide and come up with solutions myself. Currently I can find the issues but am not competent enough to come up with the solutions. But I'm learning :) Thanks again Dave I think the advice Jan gave you was solid .. I'm not entirely convinced that combining it into one query would hold any benefit. There are times when 3 relatively efficient queries can be better than one big table scan for example. I recon if you manage to optimise the main culprits then the 3 queries should reduce to a managable size. And .. while we are at it please be aware that indexes are not necessarily the holy grail they seem. They take up a lot of space for one and secondly they slow down other queries .. updates etc. I'd say use indexes sparingly but well. Ultimate SEO Urls 5 PRO - Multi Language Modern, Powerful SEO Urls KissMT Dynamic SEO Meta & Canonical Header Tags KissER Error Handling and Debugging KissIT Image Thumbnailer Security Pro - Querystring protection against hackers ( a KISS contribution ) If you found my post useful please click the "Like This" button to the right. Please only PM me for paid work. Link to comment Share on other sites More sharing options...
vmt Posted February 5, 2008 Share Posted February 5, 2008 I tried installing Chemo's tax class and it seemed really simple, but I am getting the same error that ewww got several pages back. I have double and triple checked the two files you edit and everything seems to be in order, but still when I go to this page I get this error. Fatal error: Call to a member function getTaxRate() on a non-object in /var/www/catalog/includes/functions/general.php on line 340 Does anyone know how to solve this problem? Thank you in advance Link to comment Share on other sites More sharing options...
Jan Zonjee Posted February 5, 2008 Share Posted February 5, 2008 Fatal error: Call to a member function getTaxRate() on a non-object in /var/www/catalog/includes/functions/general.php on line 340 Sounds like the tax class is not being loaded in application_top.php. Link to comment Share on other sites More sharing options...
vmt Posted February 5, 2008 Share Posted February 5, 2008 Sounds like the tax class is not being loaded in application_top.php. Thank you Jan. Because of your comment, I played with the general.php file a bit more and I got it to work after I moved the require for the tax class to before my require for STS and it worked. Thanks again. Link to comment Share on other sites More sharing options...
♥stubbsy Posted February 5, 2008 Share Posted February 5, 2008 I think the advice Jan gave you was solid .. I'm not entirely convinced that combining it into one query would hold any benefit. There are times when 3 relatively efficient queries can be better than one big table scan for example. I recon if you manage to optimise the main culprits then the 3 queries should reduce to a managable size. And .. while we are at it please be aware that indexes are not necessarily the holy grail they seem. They take up a lot of space for one and secondly they slow down other queries .. updates etc. I'd say use indexes sparingly but well. Hi Rob, Yes, the advice Jan gave me worked a treat, the page loads fine now. I know what you're saying about indexes but on the few occaissions I've had problems with page load times, indexes have come to the rescue :) One thing I have realised (as one of the solutions you have suggested are already incorporated in more recent releases) is that it is time i started working through my site and updating it from its current august 2006 release (which is the last time I updated it). but you know how it is, my site is probably about 4 - 5 years work in progress and there are so many modification that have been made that I wouldn't know where to start listing them all, let alone find the time to work through the upgrades :) Anyway, thanks again for your help All the best Dave Link to comment Share on other sites More sharing options...
ShoppingMantra Posted February 7, 2008 Share Posted February 7, 2008 What does PHP tell you: No such file or directory Hi Don't Mind but I don't know PHP so please describe how can I fix this error. Link to comment Share on other sites More sharing options...
Jan Zonjee Posted February 7, 2008 Share Posted February 7, 2008 Hi Don't Mind but I don't know PHP so please describe how can I fix this error. So? Common sense is then not applicable? PHP tells you the file or directory is not there. Common sense is then that you see what PHP complains about and then make sure the file or directory is there. This thread is not about a contribution by the way and your post tells me you are trying to set up logging/show parse time in admin->Configuration->Logging. There is an entry there that is called: Log Destination /var/log/www/tep/page_parse_time.log. You can edit it to point to a directory you have to make yourself, preferably not tep because hackers expect it to be there, and that can be written to by the web server. Or perhaps uploading an empty text file named page_parse_time.log that is writable for the webserver is sufficient. Never tried it myself. Link to comment Share on other sites More sharing options...
scranmer Posted March 12, 2008 Share Posted March 12, 2008 OK here is my input on the subject. I have found 2 others but need to sift them out again so Ill hopefully add them later. I have at least one customer with lots of reviews and the random review needed optimizing. Only way I have found to give this a proper speed boost is to use the following. Basically i have ensured when at the product level the product is the first selection. If no product number, so you are viewing index or a category, it then finds the greatest review id and uses it to randomly pick 10 numbers and directly read the records, then allows osc to randomly select from this list. It picks 10 so that the standard osc code and randomly select a record but it also then caters for you having removed any invalid reviews. As an added extra, there is a variable you can set ($min_review_perc) to force the random selection to be in the top x? of the reviews, meaning you always get a recent review. HTH Si. new infoBoxHeading($info_box_contents, false, false, tep_href_link(FILENAME_REVIEWS)); //**iBridge 07-10-29 SQL efficiency // $random_select = "select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, " . TABLE_PRODUCTS . " p, " . TABLE_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 = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'"; if (isset($HTTP_GET_VARS['products_id'])) { $random_select = "select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and p.products_status = '1' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and rd.languages_id = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'". " order by r.reviews_id desc limit " . MAX_RANDOM_SELECT_REVIEWS; } else { //find last id $max_review_query = tep_db_query("select MAX(reviews_id) maxid from " . TABLE_REVIEWS); $max_review = tep_db_fetch_array($max_review_query); //if you have lots of reviews and want a minimum id so that it only selects the more recent reviews then set this to .5 (for 50%) //leave as 1 to select from all reviews $min_review_perc = 1; ///$min_review_perc = .7; //top 70% //find 10 random ids $review_ids = "r.reviews_id IN ( " . (int)tep_rand((int)($max_review['maxid']*$min_review_perc), $max_review['maxid']) .', '. (int)tep_rand((int)($max_review['maxid']*$min_review_perc), $max_review['maxid']) .', '. (int)tep_rand((int)($max_review['maxid']*$min_review_perc), $max_review['maxid']) .', '. (int)tep_rand((int)($max_review['maxid']*$min_review_perc), $max_review['maxid']) .', '. (int)tep_rand((int)($max_review['maxid']*$min_review_perc), $max_review['maxid']) .', '. (int)tep_rand((int)($max_review['maxid']*$min_review_perc), $max_review['maxid']) .', '. (int)tep_rand((int)($max_review['maxid']*$min_review_perc), $max_review['maxid']) .', '. (int)tep_rand((int)($max_review['maxid']*$min_review_perc), $max_review['maxid']) .', '. (int)tep_rand((int)($max_review['maxid']*$min_review_perc), $max_review['maxid']) .', '. (int)tep_rand((int)($max_review['maxid']*$min_review_perc), $max_review['maxid']) . " ) and"; //build the sql $random_select = "select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where ". $review_ids . " p.products_status = '1' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and rd.languages_id = '" . (int)$languages_id . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'". " order by r.reviews_id desc"; } // if (isset($HTTP_GET_VARS['products_id'])) { // $random_select .= " and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "'"; // } // $random_select .= " order by r.reviews_id desc limit " . MAX_RANDOM_SELECT_REVIEWS; //**iBridge end $random_product = tep_random_select($random_select); Link to comment Share on other sites More sharing options...
New 
Recommended Posts
Archived
This topic is now archived and is closed to further replies.