radhavallabh Posted May 26, 2017 Share Posted May 26, 2017 Hi; I am using oscommerce 2.3.4 I have already optimized the tax queries and category count queries (Switched it off in admin too), I read through many speed optimization posts of the forum which helped me wonderfully to get the other ones down Installed http://addons.oscommerce.com/info/2417 And Installed Chemo Compress But these below category based queries are taking up a lot of time- They are approximately 300 or more- Example Category Query from KISSIT debugger- select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = '267' and c.categories_id = cd.categories_id and c.status_categ = '1' and cd.language_id = '1' order by sort_order, cd.categories_name Please could someone help me get these queries highly optimized, I am not able to figure out on how to get this working. Any help would be highly appreciated. Thank you in advance; Very Warm Regards/ radhavallabh Link to comment Share on other sites More sharing options...
frankl Posted May 26, 2017 Share Posted May 26, 2017 Do the c.categories_id and cd.categories_id columns have an index on them in your database? osCommerce user since 2003! Link to comment Share on other sites More sharing options...
radhavallabh Posted May 27, 2017 Author Share Posted May 27, 2017 3 hours ago, frankl said: Do the c.categories_id and cd.categories_id columns have an index on them in your database? Thank you so much for responding and guiding me- Yes I do have the index for them- I have below Keyname, Type and Field in Indexes Section of the Category Table- PRIMARY PRIMARY categories_id idx_categories_parent_id INDEX parent_id categories_id INDEX categories_id I have below Keyname, Type and Field in Indexes Section of the Category Description Table- PRIMARY PRIMARY categories_id language_id idx_categories_name INDEX categories_name categories_id INDEX categories_id Link to comment Share on other sites More sharing options...
♥bruyndoncx Posted May 27, 2017 Share Posted May 27, 2017 if you have 300 of those, I'm guessing you are building your category tree and not using the category tree class to generate some kind of menu or display of categories on the home page ? KEEP CALM AND CARRY ON I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support). So if you are still here ? What are you waiting for ?! Find the most frequent unique errors to fix: grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt Link to comment Share on other sites More sharing options...
radhavallabh Posted May 27, 2017 Author Share Posted May 27, 2017 Hi, 25 minutes ago, bruyndoncx said: if you have 300 of those, I'm guessing you are building your category tree and not using the category tree class to generate some kind of menu or display of categories on the home page ? Thank you so much for helping me and guiding me through..... I am pasting below the script I use to fetch the categories and generate the menu- <?php $categories_query = tep_db_query("select distinct c.categories_id, c.parent_id, cd.categories_id, cd.categories_name from " . TABLE_CATEGORIES . " c, ".TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = cd.categories_id and c.parent_id = '0' and c.status_categ = '1' and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name "); if (tep_db_num_rows($categories_query) > 0) { $nav_count = 0; <ul> <li ><a href="<?php echo tep_href_link(FILENAME_DEFAULT); ?>">Home</a></li> while ($cat = tep_db_fetch_array($categories_query)) { $cPath_new = 'cPath=' .$cat['categories_id']; /* check category name for current language */ if ($cat['categories_name'] !== "") { $nav_count ++; $current_cat_id = $cat['categories_id']; $categories_query_sub = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_cat_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name"); ?> ----------Categories output--------------- .......... then futher generation html continues...... Thank you so much again for helping me... Hope I get this resolved soon with the guidance of you all; Very warm Regds./ radhavallabh Link to comment Share on other sites More sharing options...
radhavallabh Posted May 27, 2017 Author Share Posted May 27, 2017 1 hour ago, bruyndoncx said: if you have 300 of those, I'm guessing you are building your category tree and not using the category tree class to generate some kind of menu or display of categories on the home page ? Hi, I went into more detail research on Category tree function - Yes I am not using it all though it so cool... May be I overlooked it. But now the main issue arising is how shall I incorporate it with my current custom-category.php file. It is called directly in the header. And all category generation takes place in it. I went through But the main problem I am facing that the beautiful category tree function used above in a set combination of html, css and js. Whereas Mine is a different layout using different css and html. Also I have 3 tier sublevels. Is it possible to call the category-tree seperately for main parent category level, subcategory level and sub-sub category level. Hence I could infuse my html in mid way of each call. I am sorry I am pretty bad at explaining this; But hope I am able to give a hint of what I am trying to achieve. Awaiting your valuable respose and input; Very warm regds/ radhavallabh Link to comment Share on other sites More sharing options...
radhavallabh Posted May 27, 2017 Author Share Posted May 27, 2017 11 hours ago, frankl said: Do the c.categories_id and cd.categories_id columns have an index on them in your database? 3 hours ago, bruyndoncx said: if you have 300 of those, I'm guessing you are building your category tree and not using the category tree class to generate some kind of menu or display of categories on the home page ? Sorry to keep posting like this- Is it possible to use category-tree as a function while fetching the categories, sub categories and subsub categories in the custom category.php file ? So the query is automatically reduced. Link to comment Share on other sites More sharing options...
radhavallabh Posted May 27, 2017 Author Share Posted May 27, 2017 Hi, I also forgot to mention I use the Hide Categories with Cache addon http://addons.oscommerce.com/info/8921... So should not these category queries be cached already and hence every time these queries shall not be made. Please do guide me if I am wrong on this as I do not have that much of knowledge. Awaiting your valuable response; Warm Regds/ radhavallabh Link to comment Share on other sites More sharing options...
radhavallabh Posted May 29, 2017 Author Share Posted May 29, 2017 Hi; I tried using cache for reducing queries.... I tried implementing something like below instead.. But the output file is not writing to cache- Could you please enlighten me where am I going wrong- One more thing I am also trying to add the language id to it can you guide me also on how can that be done. <?php $file = '../includes/cache/cache_custom_categories.php'; //location of cache file $current_time = time(); $cache_last_modified = filemtime($file); //time when the cache file was last modified if(file_exists($file) && ($current_time < strtotime('+1 month', $cache_last_modified))){ //check if cache file exists and hasn't expired yet include($file); //include cache file }else{ ob_start(); //start output buffering ?> <?php /* big menu */ include(DC_BLOCKS . 'custom_categories.php'); /*big menu*/ $fp = fopen($file, 'w'); //open cache file fwrite($fp, ob_get_contents()); //create new cache file fclose($fp); //close cache file ob_end_flush(); //flush output buffered } ?> Awaiting your valuable reply; Warm Regds/ radhavallabh Link to comment Share on other sites More sharing options...
radhavallabh Posted May 29, 2017 Author Share Posted May 29, 2017 Hi, Thank u for all your help.My issue has been solved using osc advanced cache class addon. Once again I would like to appreciate and thank for the guidance and support . Warm Regards/ radhavallabh Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.