Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Reducing numerous category queries for high speed on index page!!


radhavallabh

Recommended Posts

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

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

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

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

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

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

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

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

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

Archived

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

×
×
  • Create New...