Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

A Store Speed Optimization in Progress


Guest

Recommended Posts

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.

 

This is my development server and I'm going to keep the admin area open for everyone. Don't mess this up with malicious stuff or I'll lock it down.

 

On the bottom of every page there is a debug output with parse time, query count, and the actual queries used to construct the page. I'll be using this info to optimize the queries.

 

Can anyone take a look and see any redundant queries? It's a loaded question since I've already been down this block and know where there is opportunity to streamline. Let's see if anyone wants to follow along as we get this down to 10 queries per page and sub .1 second render times (read: bad ass cart performance).

 

The only requirement to the optimizations are a net increase in performance and NO LOSS OF FUNCTIONALITY.

 

Vanilla Install Stats:

Parse Time: .328 seconds

Queries - 92

 

Disable Category Counts:

Parse Time: .259 seconds

Queries: 52

 

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.

Link to comment
Share on other sites

  • Replies 905
  • Created
  • Last Reply

So now I have removed the banner code and the stats have dropped:

 

Page Render Time: .221 seconds

Queries: 47

 

Tomorrow I am going to the sources of these apparently redundant queries:

 

            [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 = '21' and language_id = '1'
           [14] => select specials_new_products_price from specials where products_id = '21' and status
           [15] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [16] => select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit 9
           [17] => select products_name from products_description where products_id = '7' and language_id = '1'
           [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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [19] => select products_name from products_description where products_id = '21' and language_id = '1'
           [20] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [21] => select products_name from products_description where products_id = '10' and language_id = '1'
           [22] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [23] => select products_name from products_description where products_id = '24' and language_id = '1'
           [24] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [25] => select products_name from products_description where products_id = '13' and language_id = '1'
           [26] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [27] => select products_name from products_description where products_id = '27' and language_id = '1'
           [28] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [29] => select products_name from products_description where products_id = '2' and language_id = '1'
           [30] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [31] => select products_name from products_description where products_id = '16' and language_id = '1'
           [32] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [33] => select products_name from products_description where products_id = '5' and language_id = '1'
           [34] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [35] => 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
           [36] => 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
           [37] => 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
           [38] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority
           [39] => 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 = '223') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '18') and tr.tax_class_id = '1' group by tr.tax_priority

Can anyone guess where they are located? :)

Link to comment
Share on other sites

First, I removed the banner (none of my sites or client sites use banners). This brought the page query count down to:

 

Page Render Time: .221 seconds

Queries: 47

 

Second, I optimized the tep_get_tax() method and also tep_get_tax_description() method. Previously, I approached the same problem and used a session based solution. In retrospect, this was a sloppy -- but highly effective -- way of decreasing the query count since each price displayed on the shopping cart used 1-2 additional queries just to get the tax rate (even if you chose to disable display with tax).

 

The code for MS3 approaches this fundamental problem of excessive queries with a nice tax class. Basically, I used the next tax class for MS3 and adapted it for MS2.

 

Page Parse Time: .179 seconds

Queries: 36

 

Now, I'm going to look at this set of queries:

            [17] => select products_name from products_description where products_id = '7' and language_id = '1'
           [18] => select products_name from products_description where products_id = '21' and language_id = '1'
           [19] => select products_name from products_description where products_id = '10' and language_id = '1'
           [20] => select products_name from products_description where products_id = '24' and language_id = '1'
           [21] => select products_name from products_description where products_id = '13' and language_id = '1'
           [22] => select products_name from products_description where products_id = '27' and language_id = '1'
           [23] => select products_name from products_description where products_id = '2' and language_id = '1'
           [24] => select products_name from products_description where products_id = '16' and language_id = '1'
           [25] => select products_name from products_description where products_id = '5' and language_id = '1'

They are all the same...basically querying for the product name. This is a sign to me that a query somewhere can be optimized to use a JOIN and not a separate method to query.

 

Where is it though? Can anyone find it before I fix it?

Link to comment
Share on other sites

something like this in product_listing.php ?

$listing['products_name'] = tep_get_products_name($listing['products_id']);

 

the actual query being defined in index.php and advanced_search_result.php

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

can you share the tax class modification you made for MS2 ?

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

For those following along the query count is now down to 27.

 

The vanilla install was 92 queries and we have gotten that down to 27 with only a few modifications. This is a 70% reduction with only 3 minor file changes and a configuration setting.

 

There are other optimization techniques I'll use in the upcoming days and also add a few popular contributions. Similiarly, I'll optimize the queries for each added contribution.

Link to comment
Share on other sites

Next thing I would do is eliminate the 'Page Requests since'. This is a useless stat, as it counts each time anyone requests a page form the site, including any and all search engine spiders.

 

Also, I think there are some very good indexes we could add to the DB to help the query time a little. Probalby won't make a huge difference with a stock store, but once a couple hundred products or more are added, this will help tremendously.

 

Other suggestions

 

- Remove tables in favor of css placement

- Enable gzip compression

- install the configuration case contribution

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

First, I removed the banner (none of my sites or client sites use banners).  This brought the page query count down to:

 

Page Render Time: .221 seconds

Queries: 47

 

Second, I optimized the tep_get_tax() method and also tep_get_tax_description() method.  Previously, I approached the same problem and used a session based solution.  In retrospect, this was a sloppy -- but highly effective -- way of decreasing the query count since each price displayed on the shopping cart used 1-2 additional queries just to get the tax rate (even if you chose to disable display with tax).

 

The code for MS3 approaches this fundamental problem of excessive queries with a nice tax class.  Basically, I used the next tax class for MS3 and adapted it for MS2.

 

Page Parse Time: .179 seconds

Queries: 36

 

Now, I'm going to look at this set of queries:

 ? ? ? ? ? ?[17] => select products_name from products_description where products_id = '7' and language_id = '1'
? ? ? ? ? ?[18] => select products_name from products_description where products_id = '21' and language_id = '1'
? ? ? ? ? ?[19] => select products_name from products_description where products_id = '10' and language_id = '1'
? ? ? ? ? ?[20] => select products_name from products_description where products_id = '24' and language_id = '1'
? ? ? ? ? ?[21] => select products_name from products_description where products_id = '13' and language_id = '1'
? ? ? ? ? ?[22] => select products_name from products_description where products_id = '27' and language_id = '1'
? ? ? ? ? ?[23] => select products_name from products_description where products_id = '2' and language_id = '1'
? ? ? ? ? ?[24] => select products_name from products_description where products_id = '16' and language_id = '1'
? ? ? ? ? ?[25] => select products_name from products_description where products_id = '5' and language_id = '1'

They are all the same...basically querying for the product name.  This is a sign to me that a query somewhere can be optimized to use a JOIN and not a separate method to query.

 

Where is it though?  Can anyone find it before I fix it?

 

 

Chemo

 

umm I am new to php and such and was wondering where and how you removed the banner code. was this done in the footer?

 

Could you please explain?

 

Thank you

RavenWulf

Link to comment
Share on other sites

Sure...

 

In application_top you'll find code that looks like the following around line 448 or so:

// auto activate and expire banners
require(DIR_WS_FUNCTIONS . 'banner.php');
tep_activate_banners();
tep_expire_banners();

 

Replace it with this code:

// auto activate and expire banners
#  require(DIR_WS_FUNCTIONS . 'banner.php');
#  tep_activate_banners();
#  tep_expire_banners();

 

Notice, I used the old style # comment tag. Yes, hard habit are hard to break (for those that know wha I mean).

 

Next, in footer.php you'll find code that looks like this:

<?php
 if ($banner = tep_banner_exists('dynamic', '468x50')) {
?>
<br>
<table border="0" width="100%" cellspacing="0" cellpadding="0">
 <tr>
   <td align="center"><?php #echo tep_display_banner('static', $banner); ?></td>
 </tr>
</table>
<?php
 }
?>

Change it to this:

<?php
 #if ($banner = tep_banner_exists('dynamic', '468x50')) {
?>
<!-- remove banner code
<br>
<table border="0" width="100%" cellspacing="0" cellpadding="0">
 <tr>
   <td align="center"><?php #echo tep_display_banner('static', $banner); ?></td>
 </tr>
</table>
//-->
<?php
 #}
?>

Notice, I used the classic HTML comment tag and also the old style comment # for the PHP.

 

This change netted us 5 reduced queries per page.

 

As a side note, have you thought about installing a debug code on your site to help with optimizing? It is the same kind of output on the bottom of the dev site.

Link to comment
Share on other sites

This is so interesting! I'll be following this development.

many of my visitors arrive via dialup, so load time is important to me.

I can only add contribs- I don't understand how to modify code. I removed the date and visitor counter first thing, is there a queriy that I need to remove also? I don't see anything in application_top.php

psst... wanna buy a wand?

Link to comment
Share on other sites

Chemo, you sir are a legend. :)

 

I've been wading through the other optimisation thread looking for ways of cutting queries and upping speed for my (heavily customised) site. It's great to find a thread that starts off right at the basics - the base code.

 

Keep up the good work!

Link to comment
Share on other sites

First, I removed the banner (none of my sites or client sites use banners).  This brought the page query count down to:

 

Page Render Time: .221 seconds

Queries: 47

 

Second, I optimized the tep_get_tax() method and also tep_get_tax_description() method.  Previously, I approached the same problem and used a session based solution.  In retrospect, this was a sloppy -- but highly effective -- way of decreasing the query count since each price displayed on the shopping cart used 1-2 additional queries just to get the tax rate (even if you chose to disable display with tax).

 

The code for MS3 approaches this fundamental problem of excessive queries with a nice tax class.  Basically, I used the next tax class for MS3 and adapted it for MS2.

 

Page Parse Time: .179 seconds

Queries: 36

 

Now, I'm going to look at this set of queries:

 ? ? ? ? ? ?[17] => select products_name from products_description where products_id = '7' and language_id = '1'
? ? ? ? ? ?[18] => select products_name from products_description where products_id = '21' and language_id = '1'
? ? ? ? ? ?[19] => select products_name from products_description where products_id = '10' and language_id = '1'
? ? ? ? ? ?[20] => select products_name from products_description where products_id = '24' and language_id = '1'
? ? ? ? ? ?[21] => select products_name from products_description where products_id = '13' and language_id = '1'
? ? ? ? ? ?[22] => select products_name from products_description where products_id = '27' and language_id = '1'
? ? ? ? ? ?[23] => select products_name from products_description where products_id = '2' and language_id = '1'
? ? ? ? ? ?[24] => select products_name from products_description where products_id = '16' and language_id = '1'
? ? ? ? ? ?[25] => select products_name from products_description where products_id = '5' and language_id = '1'

They are all the same...basically querying for the product name.  This is a sign to me that a query somewhere can be optimized to use a JOIN and not a separate method to query.

 

Where is it though?  Can anyone find it before I fix it?

 

 

I believe it is in the new_products module where function tep_get_products_name($new_products['products_id']); is called in a loop. Makes no sense since the product name is already retrieved in the products query.

I commented that function out without problems.

 

PS. great contribution "page_cache", improves a lot. Maybe one could filter out the possible session_id's from the parameters.

 

Amanda.

Treasurer MFC

Link to comment
Share on other sites

What kind of code would i use to show this: Queries: 52

 

thanks.  Mod seems to be working great but would love to see how many queries it cut down

I'll put it into a contribution and post it...should be a quick and install.

 

This is so interesting! I'll be following this development.

many of my visitors arrive via dialup, so load time is important to me.

I can only add contribs- I don't understand how to modify code. I removed the date and visitor counter first thing, is there a queriy that I need to remove also? I don't see anything in application_top.php

There are several areas that can be optimized for your particular site! Some want the banner code whereas others don't. Some love the hit counter at the bottom whereas others don't. See the trend? The stock osCommerce installation has them all in there so someone does not have to add but if they don't need them it just kills the query count.

 

Tell me what you want disabled and I'll give you the code to take it out...

 

Chemo, you sir are a legend. :)

 

I've been wading through the other optimisation thread looking for ways of cutting queries and upping speed for my (heavily customised) site. It's great to find a thread that starts off right at the basics - the base code.

 

Keep up the good work!

Thank you very much :) Wait until you see code that I haven't released yet that will bring the count down to 5 queries and sub .1 second page load times!

 

I believe it is in the new_products module where function tep_get_products_name($new_products['products_id']); is called in a loop. Makes no sense since the product name is already retrieved in the products query.

I commented that function out without problems.

 

PS. great contribution "page_cache", improves a lot. Maybe one could filter out the possible session_id's from the parameters.

 

Amanda.

YOU ARE CORRECT! Use a LEFT JOIN to pull the name as well and it eliminates a number of queries!

Link to comment
Share on other sites

BTW, if you use the query and parse time debug contribution in combination with the page cache you'll have to add it as a global variable in the cache_this_page() method of the includes/classes/page_cache.php file.

 

For instance, the cache_this_page() method should look like this:

/*###############################################################################
###

cache_this_page() method
- no arguments
- no return

################################################################################
###*/
function cache_this_page () {
 global $debug;  
 if ($this->is_guest && ENABLE_PAGE_CACHE =='true') {
	 $this->debug_output['is_guest_check'] = 'customer_id not set - cache_this_page()';
	 if ( $this->cache_file_exists && !$this->cache_file_is_expired ) {
   $this->debug_output['file_exists_and_is_not_expired'] = 'file exists and is not expired';
   echo str_replace("<%CART_CACHE%>", $this->customer_cart, file_get_contents($this->cache_file) );    
   $this->debug();
   include_once (DIR_WS_INCLUDES . 'application_bottom.php');
   exit;
	 } # END file exists and is not expired 	 
	 if ( !$this->cache_file_exists || $this->cache_file_is_expired ) {
   $this->debug_output['no_file_or_expired'] = 'file does not exist or is expired'; 
   $this->write_cache_file = true;
   $this->output_ob = true;
   ob_start();
   $this->debug_output['ob_started'] = 'ob started @ '. time();
	 } # END is file does not exist or is expired 	 
 } # END if $this->is_guest  
} # END cache_this_page method

NOTICE: all I did was add the $debug as global at the top!

Link to comment
Share on other sites

I have added my page cache contribution and here are the stats:

 

Page Parse Time: .082 seconds

Queries: 8

 

As of now, I only have the index.php file cached so product_info.php and others are still fully generated. However, you can see the benefit of having the page cached.

 

In addition, I have documented each change to the code which can be viewed at the bottom of the page. The info contained is: name of change, parse time, query count, and reference (download link, reference link, etc.) so that others can go directly to the contribution.

 

Some contributions have decreased the query count considerable and others not so much. However, each is important for their own reason.

 

Next, I'll start working on the category pages and reviews.

 

My goal here is to get each page of the store down to about 10 queries (no more than) and sub .1 rended times.

 

Anyone interested in following along?

Link to comment
Share on other sites

I'll put it into a contribution and post it...should be a quick and install.

There are several areas that can be optimized for your particular site!  Some want the banner code whereas others don't.  Some love the hit counter at the bottom whereas others don't.  See the trend?  The stock osCommerce installation has them all in there so someone does not have to add but if they don't need them it just kills the query count.

 

Tell me what you want disabled and I'll give you the code to take it out...

Thank you very much :)  Wait until you see code that I haven't released yet that will bring the count down to 5 queries and sub .1 second page load times!

YOU ARE CORRECT!  Use a LEFT JOIN to pull the name as well and it eliminates a number of queries!

 

 

Chemo, Thank you for all of this! I was looking into how to speed things up and all since I have a very modded store and want it as speedy as it can be. On that, I am still very new at all of this (great at adding contribs and getting things working, but all the rest is still hard work), and was wondering if you could give detailed instructions of what you have done, where, and how? such as the join function, how do I use it, where, and what does it do? (sorry I like to know more about things than just do this and it will work, I like to know why LOL)

 

Thank you again for all of this, I eagerly await more!

 

RavenWulf

Link to comment
Share on other sites

I would but I have 2 excuses

- being a developer, so it wouldn't prove 3.

- got enable/disable categories installed

 

But you can have my code when you release the contribution :)

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

I would but I have 2 excuses

- being a developer, so it wouldn't prove 3.

- got enable/disable categories installed

 

But you can have my code when you release the contribution :)

 

 

what does have enable/disable categories contribution have to do with it? I ask because I also have this installed.

 

RW

Link to comment
Share on other sites

I would but I have 2 excuses

- being a developer, so it wouldn't prove 3.

- got enable/disable categories installed

 

But you can have my code when you release the contribution :)

If you're a developer you'll no doubt recognize some of the basic things I'm doing here. This about it like this: if you can save 84 queries per page how many would that save in a year? Assuming 1,000 visitors per day (spiders included) and 3 pages viewed per visit it would save 9,198,000 queries per year.

 

If you're like me and have a dedicated server with several (50+) osCommerce sites installed that would mean almost half a billion queries!! I'm doing this purely to save my MySQL server...it's cheaper than getting a dedicated MySQL server.

 

BTW, even if you have enable/disable categories installed (which I do on several sites) the improvements here will give the same benefits and are completely compatible.

 

what does have enable/disable categories contribution have to do with it? I ask because I also have this installed.

 

RW

The only way that it will be affected is if you install the page cache. This is because the page is cached and will be served until it expires. You can set the timelife in the admin control panel and can be anywhere from 1 minute to 1 year.

Link to comment
Share on other sites

If you're a developer you'll no doubt recognize some of the basic things I'm doing here.  This about it like this: if you can save 84 queries per page how many would that save in a year?  Assuming 1,000 visitors per day (spiders included) and 3 pages viewed per visit it would save 9,198,000 queries per year. 

 

If you're like me and have a dedicated server with several (50+) osCommerce sites installed that would mean almost half a billion queries!!  I'm doing this purely to save my MySQL server...it's cheaper than getting a dedicated MySQL server.

 

BTW, even if you have enable/disable categories installed (which I do on several sites) the improvements here will give the same benefits and are completely compatible. 

The only way that it will be affected is if you install the page cache.  This is because the page is cached and will be served until it expires.  You can set the timelife in the admin control panel and can be anywhere from 1 minute to 1 year.

 

 

so even if I have the enable/disable installed all I would need to do is anytime I change what catagories are there "flush" the cache and recreate it? Is there an admin function to page cache to do this? or is it simple set it to expire in a min, wait, then reenable it for however long I want?

 

Thank

RW

Link to comment
Share on other sites

so even if I have the enable/disable installed all I would need to do is anytime I change what catagories are there "flush" the cache and recreate it?  Is there an admin function to page cache to do this? or is it simple set it to expire in a min, wait, then reenable it for however long I want?

 

Thank

RW

There is an auto cache flush feature built in....go to the testing server and then to the admin directory. I don't have it password protected as I have faith in the osC community to not do malicious things.

 

Under the Configuration setting group find the Page Cache Settings. Next, find the delete cache files option. Set it to "true" and update. On the very first page call after that the files will be deleted and the value in the database set back to false.

 

So, it is an automatic function to delete the cache files.

 

Go try it out :)

Link to comment
Share on other sites

Hello, I also want to install this nice Contribution.

 

 

However, have a problem with Install?

 

STEP 3 (catalog,includes,funktions, general.php)

 

I found the Code not in my File.

 function tep_db_query($query, $link = 'db_link') {
     global $$link;
     
     if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) {
     error_log('QUERY ' . $query . "\n", 3, STORE_PAGE_PARSE_TIME_LOG);
     }
     
     $result = mysql_query($query, $$link) or tep_db_error($query, mysql_errno(), mysql_error());
     
     if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) {
     $result_error = mysql_error();
     error_log('RESULT ' . $result . ' ' . $result_error . "\n", 3, STORE_PAGE_PARSE_TIME_LOG);
     }
     
     return $result;
     }

 

Can you help me with the Installation?

 

LG

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...