A Store Speed Optimization in Progress

[♥stubbsy]

[♥FWR Media]

[♥stubbsy]

Thats great thanks,

 

I can live with 4 seconds, it was getting embarrassing when customer phoned up, gave an order number and there was a 20 second pause on the phones before their order could be discussed!

 

Your help is much appreciated (as always :) )

 

Thanks again

 

Dave

 

P.s thanks for the tip, i'll have a read up on how to do that

[sabdullah]

Hello,

 

I know this may not exactly be an ideal forum for me to post this but I'm getting a little frustrated with a shipping module contribution (USPS Methods 3.0) that I need for my site. It seemed to be working fine until I noticed that it would cause my checkout_shipping.php page to load in 60+ seconds. I've disabled it for now but I need the USPS quotes and have lost several customers because of the slow load time. I posted my comments in the forum for the USPS Methods contribution but I don't think anyone over there has an idea what the problem stems from.

 

I know that the response from USPS are coming in almost immediately because there is an email that comes to my account whenever the quote is received. So the problem must be in my code somewhere.

 

Does anyone have any idea where to look or how to start to debug this?

 

Thanks

[homewetbar]

Hello,

 

I know this may not exactly be an ideal forum for me to post this but I'm getting a little frustrated with a shipping module contribution (USPS Methods 3.0) that I need for my site. It seemed to be working fine until I noticed that it would cause my checkout_shipping.php page to load in 60+ seconds. I've disabled it for now but I need the USPS quotes and have lost several customers because of the slow load time. I posted my comments in the forum for the USPS Methods contribution but I don't think anyone over there has an idea what the problem stems from.

 

I know that the response from USPS are coming in almost immediately because there is an email that comes to my account whenever the quote is received. So the problem must be in my code somewhere.

 

Does anyone have any idea where to look or how to start to debug this?

 

Thanks

 

Likely it is the USPS site that is taking so long to query. Use a non-live method like a shipping table contrib that way if their server is down for maintaince (which happens upon occassion) your visitors will still be able to checkout.

[Guest]

I'm running a store that has had some of these speed optimizations added, specifically OSCMax. I have been trying to add the contrib for the NY State county Taxes (http://www.oscommerce.com/community/contributions,2792/), and I have basically run out of talent.

 

The addition of the contrib seems to be pretty straightforward until we get to the changes that apply to the tep_get_tax_rate() functions inside of the original MS2 version. OSCM has implemented the tep_get_tax_rate optomization listed previously here, and it has split off the function into a class - class/tax.php.

 

I was wondering if someone might be able to take a peek at the difference in code and help me make the adjustments.

 

These are the instructions for making the changes:

 

in includes/functions/general.php

CHANGE TO:
--------------------------------------------------------------------------------
<?php
////BOF New York State Tax Modification
// Returns the tax rate for a zone / class
// TABLES: tax_rates, zones_to_geo_zones

 function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1, $tax_zip_code ="") {
global $customer_zone_id, $customer_country_id;

if ( ($country_id == -1) && ($zone_id == -1) ) {
  if (!tep_session_is_registered('customer_id')) {
	$country_id = STORE_COUNTRY;
	$zone_id = STORE_ZONE;
  } else {
	$country_id = $customer_country_id;
	$zone_id = $customer_zone_id;
  }
}

	$tax_zip_code = trim($tax_zip_code); // This trims any spaces they may have entered after/begin the zip
	if(strlen($tax_zip_code) > 5){
		$tax_zip_code = substr($tax_zip_code,0,5); // This cuts the zip down to the first 5 digits
	}

$county_taxquery = tep_db_query("select zip_tax_rate from  " . TABLE_ZIPTAX . " where zip_code =  '" . $tax_zip_code . "'");
if (tep_db_num_rows($county_taxquery)) {

$county_tax_query = tep_db_fetch_array($county_taxquery);
$county_taxrate = $county_tax_query['zip_tax_rate'];
} else {
$county_taxrate = "";
}

$tax_query = tep_db_query("select sum(tax_rate) as tax_rate from " . TABLE_TAX_RATES . " tr left join " . TABLE_ZONES_TO_GEO_ZONES . " za on (tr.tax_zone_id = za.geo_zone_id) left join " . TABLE_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 = '" . (int)$country_id . "') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '" . (int)$zone_id . "') and tr.tax_class_id = '" . (int)$class_id . "' group by tr.tax_priority");
if (tep_db_num_rows($tax_query)) {
  $tax_multiplier = 0.0;
  while ($tax = tep_db_fetch_array($tax_query)) {
	$tax_multiplier += $tax['tax_rate'];
  }
  if ($county_taxrate != "") {
	  $tax_multiplier += $county_taxrate;
  }
  return $tax_multiplier;
} else {

if ($county_taxrate != "") {
	  $tax_multiplier = $county_taxrate;
	return $tax_multiplier;
  }
  else
{
return 0;
}


}
 }


////
// Return the tax description for a zone / class
// TABLES: tax_rates;
/* test */
 function tep_get_tax_description($class_id, $country_id, $zone_id, $tax_zip_code = "") {
$county_taxquery = tep_db_query("select zip_tax_rate, zipcounty from  " . TABLE_ZIPTAX . " where zip_code =  '" . $tax_zip_code . "'");
if (tep_db_num_rows($county_taxquery)) {
$county_tax_query = tep_db_fetch_array($county_taxquery);
$county_taxrate = number_format($county_tax_query['zip_tax_rate'], 2, '.', '');
$county = $county_tax_query['zipcounty'];
} else {
$county_taxrate = "";
$county = "";
}

$tax_query = tep_db_query("select tax_description from " . TABLE_TAX_RATES . " tr left join " . TABLE_ZONES_TO_GEO_ZONES . " za on (tr.tax_zone_id = za.geo_zone_id) left join " . TABLE_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 = '" . (int)$country_id . "') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '" . (int)$zone_id . "') and tr.tax_class_id = '" . (int)$class_id . "' order by tr.tax_priority");
if (tep_db_num_rows($tax_query)) {
  $tax_description = '';
  while ($tax = tep_db_fetch_array($tax_query)) {
	$tax_description .= $tax['tax_description'] . ' + ';
  }
  $tax_description = substr($tax_description, 0, -3);
  if ($county_taxrate != "") {
	   $tax_description .= " + $county County Tax $county_taxrate%";
  }
  return $tax_description;

} else {

if ($county_taxrate != "") {
	  $tax_description = "$county County Tax $county_taxrate%";
	return $tax_description;

  }
else {
  return TEXT_UNKNOWN_TAX_RATE;
}
}
 }

								////////EOF New York State Tax Modification

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

 

IN the OSCMAX General.php:

 

////
// Returns the tax rate for a zone / class
// TABLES: tax_rates, zones_to_geo_zones
 function tep_get_tax_rate($class_id, $country_id = -1, $zone_id = -1) {
// BOF: MOD - Separate Pricing Per Customer, show_tax modification
global $customer_zone_id, $customer_country_id, $osC_Tax, $sppc_customer_group_tax_exempt;

 if(!tep_session_is_registered('sppc_customer_group_tax_exempt')) {
 $customer_group_tax_exempt = '0';
 } else {
 $customer_group_tax_exempt = $sppc_customer_group_tax_exempt;
 }

 if ($customer_group_tax_exempt == '1') {
	 return 0;
 }
return $osC_Tax->getTaxRate($class_id, $country_id, $zone_id);
}

// EOF: MOD - Separate Pricing Per Customer, show_tax modification


////
// Return the tax description for a zone / class
// TABLES: tax_rates;
 function tep_get_tax_description($class_id, $country_id, $zone_id) {
// BOF: MOD - Separate Pricing Per Customer, show_tax modification
   global $osC_Tax;
   return $osC_Tax->getTaxRateDescription($class_id, $country_id, $zone_id);
// EOF: MOD - Separate Pricing Per Customer, show_tax modification
}

 

 

And in the OSCMax class/tax.php:

 

 

 

 class osC_Tax {
var $tax_rates;

// class constructor
function osC_Tax() {
  $this->tax_rates = array();
}

// class methods
function getTaxRate($class_id, $country_id = -1, $zone_id = -1) {
// LINE ADDED: Bugfix 0000036 
global $customer_zone_id, $customer_country_id;
if ( ($country_id == -1) && ($zone_id == -1) ) {
  if (!tep_session_is_registered('customer_id')) {
	$country_id = STORE_COUNTRY;
	$zone_id = STORE_ZONE;
  } else {
	$country_id = $customer_country_id;
	$zone_id = $customer_zone_id;
  }
}

  if (isset($this->tax_rates[$class_id][$country_id][$zone_id]['rate']) == false) {
	$tax_query = tep_db_query("select sum(tax_rate) as tax_rate from " . TABLE_TAX_RATES . " tr left join " . TABLE_ZONES_TO_GEO_ZONES . " za on (tr.tax_zone_id = za.geo_zone_id) left join " . TABLE_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 = '" . (int)$country_id . "') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '" . (int)$zone_id . "') and tr.tax_class_id = '" . (int)$class_id . "' group by tr.tax_priority");
	if (tep_db_num_rows($tax_query)) {
	  $tax_multiplier = 1.0;
	  while ($tax = tep_db_fetch_array($tax_query)) {
		$tax_multiplier *= 1.0 + ($tax['tax_rate'] / 100);
	  }

	  $tax_rate = ($tax_multiplier - 1.0) * 100;
	} else {
	  $tax_rate = 0;
	}

	$this->tax_rates[$class_id][$country_id][$zone_id]['rate'] = $tax_rate;
  }

  return $this->tax_rates[$class_id][$country_id][$zone_id]['rate'];
}

function getTaxRateDescription($class_id, $country_id, $zone_id) {
  if (isset($this->tax_rates[$class_id][$country_id][$zone_id]['description']) == false) {
	$tax_query = tep_db_query("select tax_description from " . TABLE_TAX_RATES . " tr left join " . TABLE_ZONES_TO_GEO_ZONES . " za on (tr.tax_zone_id = za.geo_zone_id) left join " . TABLE_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 = '" . (int)$country_id . "') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '" . (int)$zone_id . "') and tr.tax_class_id = '" . (int)$class_id . "' order by tr.tax_priority");
	if (tep_db_num_rows($tax_query)) {
	  $tax_description = '';

	  while ($tax = tep_db_fetch_array($tax_query)) {
		$tax_description .= $tax['tax_description'] . ' + ';
	  }

	  $this->tax_rates[$class_id][$country_id][$zone_id]['description'] = substr($tax_description, 0, -3);
	} else {
	  $this->tax_rates[$class_id][$country_id][$zone_id]['description'] = TEXT_UNKNOWN_TAX_RATE;
	}
  }

  return $this->tax_rates[$class_id][$country_id][$zone_id]['description'];
}
 }

 

The biggest issue that I have in understanding this is that there is a difference in coding where the class uses a lot of "this->" statement. I'm not sure how those work in comparison to the standard "return=0" type of statement. Any help would be greatly appreciated. Thanks!

[♥FWR Media]

Well I've been following this excellent thread since it started and it is invaluable in creating an efficient and fast shop.

 

One of the biggest culprits for queries is of course the menu which becomes even worse when using contributions that pop out the full range of categories, some dhtml menu systems can create upwards of 70 queries per page load .. ouch.

 

Anyway I thought it may be appropriate to mention here the new pop out menu system I uploaded recently as I feel it fits in well with this topic.

 

How about a pop out menu system that holds all your categories yet costs only one query?

 

The caveat (and there always is one) is that the one query is a full table scan that will be heavy for large shops, for this reason the menu system has a "permanent" cache as opposed to a "time related" or "expiring" cache.

 

What this means is that the menu system, in daily operation, actually uses no queries at all. The cache file is included which holds the categories in serialized format . . to give you an example the size of the file is about 4kb for a vanilla install.

 

When you need to refresh the cache .. i.e you added or changed the categories, you can refresh it via the admin panel, ideally at a time when your shop is quiet.

 

Further development is planned for the menu system using the same one query and serialized file which is likely to include a categories site map and XML sitemaps.

 

Contribution:

http://addons.oscommerce.com/info/5942

 

Support topic:

http://www.oscommerce.com/forums/index.php?showtopic=302088

[GemRock]

The ultimate tip is to host your shop on a sport car class server with more than enough resources. If you have your shop hosted on a server that is driven by a horse, then, poor soul, you ahve no way to catch up with a sport car. full stop.

 

I tseted some the tricks on this thread and I could not see any noticeeable difference simply because the shop is on a powerful, decent server already. All the improvements, if any, would be undone by the internet connection or visitor's pc speed etc.

 

Get you a decent server and then talk about tuning, That'd save you alot of grief, and of course, time.

 

Ken

[Jan Zonjee]

I tseted some the tricks on this thread and I could not see any noticeeable difference simply because the shop is on a powerful, decent server already. All the improvements, if any, would be undone by the internet connection or visitor's pc speed etc.

I have seen someone add most of the things mentioned in this thread added to a site that had load times of around 20 seconds and go back to around 0.5 second.

 

Yes, that server was slow as molasses but if you are on a shared server and there are more osC sites on that server... if everybody applied those tricks that apparent horse cart might change in a sports car :)

[♥FWR Media]

The ultimate tip is to host your shop on a sport car class server with more than enough resources. If you have your shop hosted on a server that is driven by a horse, then, poor soul, you ahve no way to catch up with a sport car. full stop.

 

I tseted some the tricks on this thread and I could not see any noticeeable difference simply because the shop is on a powerful, decent server already. All the improvements, if any, would be undone by the internet connection or visitor's pc speed etc.

 

Get you a decent server and then talk about tuning, That'd save you alot of grief, and of course, time.

 

Ken

 

Not only do I not think that is the ultimate tip, I do not think it a tip at all.

 

You may be able to hide a bad site behind a costly and powerful server somewhat but that's about it.

[GemRock]

...but if you are on a shared server and there are more osC sites on that server... if everybody applied those tricks that apparent horse cart might change in a sports car :)

True. But my point includes, as well as a good server, a GOOD HOST who knows about how to BALANCING server loads and who would not be so unprofessional and stupid to put all mysql demanding site on same server. CHanging host is my ultimate tip No2.

 

when Chemo started this good tips&tricks thread, it was 2004. At that time he had a very good point (as good as many of the things he did on osc) to optimise as much as possible given the hardware high cost but yet poor power performance as compared with today's hardware advancement in areas such as CPU (are we not talking about quad-core already?) and memory (are we not talking about memory in terms of 10s of GBs?) and high speed harddrive (is it not GB out of date? TB is the term used).

 

A horse is a horse and never be a Audi A8 I am afraid...and move your shop to a A8 or similar and do the tuning - that may be worth it!

 

Btw, there is no such things as 'bad site' as stock osc goes - unless you copy&paste the same query 100 times on the same file, and load it to the server...the stock osc is just good enough on a decent server.

 

Ken

[♥GLWalker]

None the less, a good server is a good server, but this thread is about getting the database queries down as low as you can go, and that has nothing to do with your server other than the resourses you can save it. Even a good race horse can have a heart attact if not on a good diet.

[sabdullah]

Likely it is the USPS site that is taking so long to query. Use a non-live method like a shipping table contrib that way if their server is down for maintaince (which happens upon occassion) your visitors will still be able to checkout.

 

 

Thanks, I'll make sure to set that up, though I've been trying to avoid doing that for a while.

 

This is an awesome thread, i've implemented most of the suggestions here and its made a world of a difference in enhancing my customer's experience by giving them a really fast website.

 

You guys are awesome!

[gfdesigns]

Hi All,

I've been reading over this thread for a while and searching the board for some clues on how to make my site faster. My site at times is unuseable 30+ second load time for any page on the site and some times it doesn't load the page at all and I can't have that. Since this thread is so old i'm not sure what is still valid.

Can anyone give a girl a hand and take a peek at my site and set me in a good direction to improve my site.

Please take into account I had someone else make this site for me and pretty much threw up their hands when i asked about the speed of the site and blamed it on my host. Now granted I'm not willing to rule that out but i want to optimize what i can on my site and if it's still slow then I will open a ticket up with my host.

So i'm not as familiar with the Code and how it works as I should be since i paid someone else to make the site, so a little hand holding may be required since i don't want to break my site.

 

Some of the pictures are NSFW since I do sell Nipple pasties

Here is my site: My Web Store

 

Thanks in advance.

Lori

[TracyS]

The home page loaded quickly for me, I didn't browse through the rest as I can't do that at work due to the nature of your site - but I will try to remember to do so from home.

 

In the meantime - it is well worth the time to read through the entire thread and see what things you may be able to do to optimize your site, however, if the load time changes sporadically, the person who threw up their hands and said it's the host may be correct.

 

If you do have some knowledge about osCommerce code, then reading through the thread and seeing what changes you can implement can be very valuable for both your knowledge and your store's load time ;)

 

Hi All,

I've been reading over this thread for a while and searching the board for some clues on how to make my site faster. My site at times is unuseable 30+ second load time for any page on the site and some times it doesn't load the page at all and I can't have that. Since this thread is so old i'm not sure what is still valid.

Can anyone give a girl a hand and take a peek at my site and set me in a good direction to improve my site.

Please take into account I had someone else make this site for me and pretty much threw up their hands when i asked about the speed of the site and blamed it on my host. Now granted I'm not willing to rule that out but i want to optimize what i can on my site and if it's still slow then I will open a ticket up with my host.

So i'm not as familiar with the Code and how it works as I should be since i paid someone else to make the site, so a little hand holding may be required since i don't want to break my site.

 

Some of the pictures are NSFW since I do sell Nipple pasties

Here is my site: My Web Store

 

Thanks in advance.

Lori

[♥FWR Media]

Hi All,

I've been reading over this thread for a while and searching the board for some clues on how to make my site faster. My site at times is unuseable 30+ second load time for any page on the site and some times it doesn't load the page at all and I can't have that. Since this thread is so old i'm not sure what is still valid.

Can anyone give a girl a hand and take a peek at my site and set me in a good direction to improve my site.

Please take into account I had someone else make this site for me and pretty much threw up their hands when i asked about the speed of the site and blamed it on my host. Now granted I'm not willing to rule that out but i want to optimize what i can on my site and if it's still slow then I will open a ticket up with my host.

So i'm not as familiar with the Code and how it works as I should be since i paid someone else to make the site, so a little hand holding may be required since i don't want to break my site.

 

Some of the pictures are NSFW since I do sell Nipple pasties

Here is my site: My Web Store

 

Thanks in advance.

Lori

 

The thread is still highly valid from the first post to the last, these optimisations however require "getting your hands dirty" with code and if you are as inexperienced as you suggest I'm not sure I'd recommend it.

[GemRock]

Hi All,

...Here is my site: My Web Store

...Lori

to be honest your site reminds me of internet's stone age-all the indication that you may well be on a stone age server with a PIII pc or the host manages to pack several hundreds of sites on one single machine to get max. profit at the expense of its site owners.

 

switch to a host that can offer you a powerful server with lots of hardware resources, which would save you lots of grief. Note that the switching over to a powerful server does not necassarily mean it's cost you an arm or leg, given the fact that nowadays hardware is much much more cheap than several years ago and it will continue to become even cheaper but maccines are becoming better and better.

 

once you make that move, if you are still not happy and/or you have lots time at hand or you just feel bored and want to have something to get you busy or want to experiment then come back here and follow this thread from the first page down.

 

Ken

[gfdesigns]

all the indication that you may well be on a stone age server with a PIII pc or the host manages to pack several hundreds of sites on one single machine to get max. profit at the expense of its site owners

 

I think you might be right about this, I've been monitoring my site and it seems to be slow only during certain times of the day (which of course is the busy time of the day for everyone). I hate to have to switch hosts since i'll have to get my lazy code monkey to help me with the switch over at some point. But I'm doing some research and finding a lot of good hosts for way under what i'm paying per month with better options and support.

 

I have done a few of the tricks (got my hands dirty) on this thread and they have helped quite a bit, but during the "busy" times nothing helps. I really just wanted to cover all my bases before i made a jump. I've been with my hosting company since 2001 and worked there for 3 years so i had a bit of attachment. Right now the only thing i'm gonna keep with them in my email address.

 

Thanks to everyone for checking my site and giving me very useful advice even though it doesn't seem like much it helped kick me in the butt.

 

xoxo

Lori

[Guest]

I am looking to get some clarity on the post 610 and 611. Both of these are indexing the products_options_values_id but are written differently.

 

Jan has it as:

alter table products_options_values_to_products_options add index idx_products_options_values_id (product_options_values_id);

 

 

Robert has it as:

ALTER TABLE `products_options_values_to_products_options` ADD INDEX ( `products_options_values_id` )

 

 

 

Are these the same or are they going to accomplish different indexes. I know probably a dumb question but I am not well read on Mysql.

 

 

Thanks all

Matthew

[♥FWR Media]

I am looking to get some clarity on the post 610 and 611. Both of these are indexing the products_options_values_id but are written differently.

 

Jan has it as:

alter table products_options_values_to_products_options add index idx_products_options_values_id (product_options_values_id);

 

 

Robert has it as:

ALTER TABLE `products_options_values_to_products_options` ADD INDEX ( `products_options_values_id` )

 

 

 

Are these the same or are they going to accomplish different indexes. I know probably a dumb question but I am not well read on Mysql.

 

 

Thanks all

Matthew

 

Jans version is better as it's a good idea to add a "tag" so as not to conflict with functions .. the "tag" in Jans case is idx_ where idx_ is known not to be a function.

[GemRock]

...Right now the only thing i'm gonna keep with them in my email address...Lori

changing host does NOT have any effect on your domain emails/email addresses. Some good hosts could even do the site transfer for free as it is about 15 minutes job if you know how to.

 

Ken

[gfdesigns]

changing host does NOT have any effect on your domain emails/email addresses. Some good hosts could even do the site transfer for free as it is about 15 minutes job if you know how to.

 

Oh no, it's not the domain email address, it's my personal email address. don't you worry! And I am going to check into site transfers cause i'm tired of doing all the work. ;)

 

xoxo

[webservice]

Hello,

 

as I wrote here I have just installed the latest version of OSC (oscommerce-2.2rc2a) and moved there the data from an older version (a version since 2003-2004). Unfortunately the loading time of the new OSC its terrible. Even the old version was faster. I have read all this thread but i suppose that having installed the latest version of OSC i do not have many options in order to optimize better my cart. We host this catalog in Mediatemple and there they have a tool (Mysql Container) where you can see some analysis. Below are some of the slow queries that this tool returns.

 

Created 04:15 PM 07/14/2008

### 319 Queries
### Total time: 32883, Average time: 103.081504702194
### Taking 29 to 549 seconds to complete
### Rows analyzed 6558415 - 6560522
select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, 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, products_description pd where p.products_status = 'XXX' and p.products_id = pd.products_id and pd.language_id = 'XXX' order by p.products_date_added desc limit XXX;

select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, 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, products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '4' order by p.products_date_added desc limit 12;


### 256 Queries
### Total time: 16875, Average time: 65.91796875
### Taking 14 to 401 seconds to complete
### Rows analyzed 2695124 - 6816611
select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, 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, products_description pd, products_to_categories pXXXc, categories c where p.products_id = pXXXc.products_id and pXXXc.categories_id = c.categories_id and c.parent_id = 'XXX' and p.products_status = 'XXX' and p.products_id = pd.products_id and pd.language_id = 'XXX' order by p.products_date_added desc limit XXX;

select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, 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, products_description pd, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '638' and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_date_added desc limit 12;


### 15 Queries
### Total time: 99, Average time: 6.6
### Taking 2 to 18 seconds to complete
### Rows analyzed 133690 - 765554
select count(p.products_id) as total from products p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where p.products_status = 'XXX' and pd.products_id = p.products_id and pd.language_id = 'XXX' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = 'XXX';

select count(p.products_id) as total from products p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '4' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '46';


### 8 Queries
### Total time: 58, Average time: 7.25
### Taking 4 , 5 , 7 , 8 , 8 , 8 , 9 , 9 seconds to complete
### Rows analyzed 258150, 557143, 557143, 557163, 557183, 557203, 557223 and 557243
select p.products_model, m.manufacturers_name, p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where p.products_status = 'XXX' and pd.products_id = p.products_id and pd.language_id = 'XXX' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = 'XXX' order by pd.products_name limit XXX, XXX;

select p.products_model, m.manufacturers_name, p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '18' order by pd.products_name limit 120, 20;


### 5 Queries
### Total time: 52, Average time: 10.4
### Taking 2 , 2 , 2 , 3 , 43 seconds to complete
### Rows analyzed 5328, 5328, 5328, 5328 and 5328
select distinct p.products_id, pd.products_name from products p, products_description pd where p.products_status = 'XXX' and p.products_ordered > XXX and p.products_id = pd.products_id and pd.language_id = 'XXX' order by p.products_ordered desc, pd.products_name limit XXX;

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;


### 1 Query
### Total time: 45, Average time: 45
### Taking 45 seconds to complete
### Rows analyzed 6560522
select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, 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, products_description pd where p.products_status = 'XXX' and p.products_id = pd.products_id and pd.language_id = 'XXX' order by p.products_date_added desc limit XXX;

select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, 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, products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '4' order by p.products_date_added desc limit 12;


### 1 Query
### Total time: 38, Average time: 38
### Taking 38 seconds to complete
### Rows analyzed 6814188
select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, 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, products_description pd, products_to_categories pXXXc, categories c where p.products_id = pXXXc.products_id and pXXXc.categories_id = c.categories_id and c.parent_id = 'XXX' and p.products_status = 'XXX' and p.products_id = pd.products_id and pd.language_id = 'XXX' order by p.products_date_added desc limit XXX;

select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, 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, products_description pd, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '286' and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '4' order by p.products_date_added desc limit 12;


### 5 Queries
### Total time: 31, Average time: 6.2
### Taking 2 , 2 , 3 , 11 , 13 seconds to complete
### Rows analyzed 224372, 472887, 472887, 557123 and 771934
select p.products_model, m.manufacturers_name, p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where p.products_status = 'XXX' and pd.products_id = p.products_id and pd.language_id = 'XXX' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = 'XXX' order by pd.products_name limit XXX, XXX;

select p.products_model, m.manufacturers_name, p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '4' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '32' order by pd.products_name limit 0, 20;


### 1 Query
### Total time: 19, Average time: 19
### Taking 19 seconds to complete
### Rows analyzed 11518
select count(*) as total from products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = 'XXX' and p.products_id = pd.products_id and pd.language_id = 'XXX';

select count(*) as total from products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '4';


### 3 Queries
### Total time: 9, Average time: 3
### Taking 2 , 3 , 4 seconds to complete
### Rows analyzed 174722, 174722 and 317998
select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories pXXXc where p.products_status = 'XXX' and p.products_id = pd.products_id and pd.language_id = 'XXX' and p.products_id = pXXXc.products_id and pXXXc.categories_id = c.categories_id and ((pd.products_name like 'XXX' or p.products_model like 'XXX' or m.manufacturers_name like 'XXX') );

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%avent%' or p.products_model like '%avent%' or m.manufacturers_name like '%avent%') );


### 1 Query
### Total time: 7, Average time: 7
### Taking 7 seconds to complete
### Rows analyzed 8269
select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories pXXXc where p.products_status = 'XXX' and p.products_id = pd.products_id and pd.language_id = 'XXX' and p.products_id = pXXXc.products_id and pXXXc.categories_id = c.categories_id and ((pd.products_name like 'XXX' or p.products_model like 'XXX' or m.manufacturers_name like 'XXX' or pd.products_description like 'XXX') and (pd.products_name like 'XXX' or p.products_model like 'XXX' or m.manufacturers_name like 'XXX' or pd.products_description like 'XXX') );

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%tΕΧΝΙΤΕΣ%' or p.products_model like '%tΕΧΝΙΤΕΣ%' or m.manufacturers_name like '%tΕΧΝΙΤΕΣ%' or pd.products_description like '%tΕΧΝΙΤΕΣ%') and (pd.products_name like '%ΒΛΕΦΑΡΙΔΕΣ%' or p.products_model like '%ΒΛΕΦΑΡΙΔΕΣ%' or m.manufacturers_name like '%ΒΛΕΦΑΡΙΔΕΣ%' or pd.products_description like '%ΒΛΕΦΑΡΙΔΕΣ%') );


### 2 Queries
### Total time: 5, Average time: 2.5
### Taking 2 , 3 seconds to complete
### Rows analyzed 6549 and 6561
select distinct p.products_id, pd.products_name from products p, products_description pd, products_to_categories pXXXc, categories c where p.products_status = 'XXX' and p.products_ordered > XXX and p.products_id = pd.products_id and pd.language_id = 'XXX' and p.products_id = pXXXc.products_id and pXXXc.categories_id = c.categories_id and 'XXX' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit XXX;

select distinct p.products_id, pd.products_name from products p, products_description pd, products_to_categories p2c, categories c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '94' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10;

 

Do you have any idea of these problems? I don't know if the problem has to do with these queries or if the problem has to do with other issues. The url of this catalog is: http://tinyurl.com/5mn6z8

[Guest]

Cross posting is not allowed. Did you look in the contributions for speed opthimizing?

[webservice]

Cross posting is not allowed. Did you look in the contributions for speed opthimizing?

 

Sorry for the cross posting but I made by mistake 3 same posts so as I couldn't delete the rest I just put a link here. As far as concerning the addons, I saw some of them but as I said I think that are refering to previous versions than the latest one that I have installed.