Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

What is the Maximum Number of Products for oscommerce website?


Guest

Recommended Posts

What could cause this issue having more 30,000 products? What can be done is this a optimizing issue? I have Error log files for viewing.

A. 0.55 seconds of cpu time for HTTP Request

B. [25-Sep-2007 19:56:51] PHP Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/datpcsto/public_html/shop/includes/functions/database.php on line 103

C. PHP Fatal error: imagepng() [<a href='function.imagepng'>function.imagepng</a>]: gd-png: fatal libpng error: zlib error in /home/datpcsto/public_html/shop/admin/includes/classes/upload.php on line 237

Link to comment
Share on other sites

It depends on what CPU (Chip) the server has and how mch RAM is available to it - and also the number of sites on the same server.

 

If your website is using up too much of the CPU resources on that server then any web hosting company would tell you that you need your own dedicated server. They are not going to allow your site to consume too much of the CPU resources on the server, thus slowing down all other websites.

 

Vger

Link to comment
Share on other sites

What could cause this issue having more 30,000 products? What can be done is this a optimizing issue? I have Error log files for viewing.

A. 0.55 seconds of cpu time for HTTP Request

B. [25-Sep-2007 19:56:51] PHP Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/datpcsto/public_html/shop/includes/functions/database.php on line 103

C. PHP Fatal error: imagepng() [<a href='function.imagepng'>function.imagepng</a>]: gd-png: fatal libpng error: zlib error in /home/datpcsto/public_html/shop/admin/includes/classes/upload.php on line 237

 

ERRORS at time of CPU Exceeded Log

 

 

 

1. problem seems to stem from GET/

 

1a. Thu Sep 27 10:11:30 2007: used 0.52 seconds of cpu time for HTTP Request: www.datpcstore.com : GET /shop/PRODUCTNAME.html HTTP/1.1?

 

 

 

2. Slow Querys

 

2a. # Thu Sep 27 10:00:10 2007

# Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 16025241

use datpcsto_pcstore;

select count(p.products_id) as total from products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join products_description pd on p.products_id = pd.products_id left join specials s on p.products_id = s.products_id where pd.products_name like '%' AND p.products_status='1'

 

# Thu Sep 27 10:00:24 2007

# Query_time: 14 Lock_time: 0 Rows_sent: 25 Rows_examined: 16053008

use datpcsto_pcstore;

select p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join products_description pd on p.products_id = pd.products_id left join specials s on p.products_id = s.products_id where pd.products_name like '%' AND p.products_status='1' order by pd.products_name limit 0, 25

 

# Thu Sep 27 10:00:28 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 37405

SELECT products_id, products_weight FROM products WHERE (products_model = 'DHLBTSP25L')

 

# Thu Sep 27 10:00:29 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 37407

SELECT

p.products_id as v_products_id,

p.products_model as v_products_model,

p.products_image as v_products_image,

p.products_price as v_products_price,

p.products_weight as v_products_weight,

p.products_date_added as v_date_avail,

p.products_tax_class_id as v_tax_class_id,

p.products_quantity as v_products_quantity,

p.manufacturers_id as v_manufacturers_id,

subc.categories_id as v_categories_id

FROM

products as p,

categories as subc,

products_to_categories as ptoc

WHERE

p.products_id = ptoc.products_id AND

p.products_model = 'DH000507' AND

ptoc.categories_id = subc.categories_id

 

# Thu Sep 27 10:00:30 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 37405

SELECT products_id, products_weight FROM products WHERE (products_model = 'DHHWUG1A')

 

# Thu Sep 27 10:00:31 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 37405

SELECT

p.products_id as v_products_id,

p.products_model as v_products_model,

p.products_image as v_products_image,

p.products_price as v_products_price,

p.products_weight as v_products_weight,

p.products_date_added as v_date_avail,

p.products_tax_class_id as v_tax_class_id,

p.products_quantity as v_products_quantity,

p.manufacturers_id as v_manufacturers_id,

subc.categories_id as v_categories_id

FROM

products as p,

categories as subc,

products_to_categories as ptoc

WHERE

p.products_id = ptoc.products_id AND

p.products_model = 'DHLCD2190UXI' AND

ptoc.categories_id = subc.categories_id

 

# Thu Sep 27 10:00:32 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 37405

SELECT products_id, products_weight FROM products WHERE (products_model = 'DHD4800360')

 

# Thu Sep 27 10:00:48 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 37980

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '81' order by pd.products_name limit 0, 25

 

# Thu Sep 27 10:01:34 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

DELETE FROM cache WHERE cache_expires <= '2007-09-27 10:01:33'

 

3. PHP Warning at time of error.

 

3a. [27-Sep-2007 09:32:46] PHP Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/datpcsto/public_html/shop/includes/functions/database.php on line 103

[27-Sep-2007 09:33:03] PHP Warning: include_once(/home/datpcsto/public_html/shop/includes/check_login.php) [<a href='function.include-once'>function.include-once</a>]: failed to open stream: No such file or directory in /home/datpcsto/public_html/shop/includes/header.php on line 56

[27-Sep-2007 09:33:03] PHP Warning: include_once() [<a href='function.include'>function.include</a>]: Failed opening '/home/datpcsto/public_html/shop/includes/check_login.php' for inclusion (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/datpcsto/public_html/shop/includes/header.php on line 56

[27-Sep-2007 09:33:35] PHP Warning: include_once(/home/datpcsto/public_html/shop/includes/check_login.php) [<a href='function.include-once'>function.include-once</a>]: failed to open stream: No such file or directory in /home/datpcsto/public_html/shop/includes/header.php on line 56

[27-Sep-2007 09:33:35] PHP Warning: include_once() [<a href='function.include'>function.include</a>]: Failed opening '/home/datpcsto/public_html/shop/includes/check_login.php' for inclusion (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/datpcsto/public_html/shop/includes/header.php on line 56

Link to comment
Share on other sites

ERRORS at time of CPU Exceeded Log

1. problem seems to stem from GET/

 

1a. Thu Sep 27 10:11:30 2007: used 0.52 seconds of cpu time for HTTP Request: www.datpcstore.com : GET /shop/PRODUCTNAME.html HTTP/1.1?

mod_rewrite on Apache does tax the CPU quite a bit. Seen an example of it recently. These SEO url's made a page load twice as slow.

 

2a. # Thu Sep 27 10:00:10 2007

# Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 16025241

use datpcsto_pcstore;

select count(p.products_id) as total from products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join products_description pd on p.products_id = pd.products_id left join specials s on p.products_id = s.products_id where pd.products_name like '%' AND p.products_status='1'

It appears that a lot of queries suffer from the fact that including the table specials slows it down tremendously. Try adding an index on the table specials. You might want to take a look at that thread (A Store Speed Optimization in Progress, Step by step from a vanilla install!) to see if there are things you can add to your shop to reduce the number of queries.

 

# Thu Sep 27 10:00:28 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 37405

SELECT products_id, products_weight FROM products WHERE (products_model = 'DHLBTSP25L')

Along the same lines as above I would try adding an index on products_model. That might speed up all the other queries you posted where products_model is used too.

 

3. PHP Warning at time of error.

 

3a. [27-Sep-2007 09:32:46] PHP Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/datpcsto/public_html/shop/includes/functions/database.php on line 103

[27-Sep-2007 09:33:03] PHP Warning: include_once(/home/datpcsto/public_html/shop/includes/check_login.php) [<a href='function.include-once'>function.include-once</a>]: failed to open stream: No such file or directory in /home/datpcsto/public_html/shop/includes/header.php on line 56

The file shop/includes/check_login.php is missing or the webserver does not have permission to read it (less likely).

Link to comment
Share on other sites

Having 30,000 or so products will and most likely slow down the server and nab cpu power if you are on a shared server. Alot of webhost will stick far more people on a server then there should be so it would not only be you slowing down the server but the 100's or so others that are on the server. Mysql reponse times can vary also if there is a surge of people trying to access to Mysql or scripts trying to use it (ie for products etc).

 

Id Suggest a dedicated server if you have that many products and or a large consumer base.

 

;)

I didnt have any freakin links in my sig!!! Pm me for help any other things ;-) There!

Link to comment
Share on other sites

Where does "check_login.php" come into it? That's not an osCommerce file.

 

If you are using a loaded version of osCommerce with gazillion contributions installed then the number of MySQL queries being run is going to be enormous.

 

Vger

Link to comment
Share on other sites

mod_rewrite on Apache does tax the CPU quite a bit. Seen an example of it recently. These SEO url's made a page load twice as slow.

It appears that a lot of queries suffer from the fact that including the table specials slows it down tremendously. Try adding an index on the table specials. You might want to take a look at that thread (A Store Speed Optimization in Progress, Step by step from a vanilla install!) to see if there are things you can add to your shop to reduce the number of queries.

Along the same lines as above I would try adding an index on products_model. That might speed up all the other queries you posted where products_model is used too.

The file shop/includes/check_login.php is missing or the webserver does not have permission to read it (less likely).

Today again 10 times My account exceeds CPU usage.

 

Is there a problem with this see below:

 

# Sat Sep 29 23:00:35 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

use datpcsto_pcstore;

select categories_name from categories_description where categories_id = '514' and language_id = '1'

 

# Sat Sep 29 23:01:32 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

select configuration_key as cfgKey, configuration_value as cfgValue from configuration

 

# Sat Sep 29 23:04:36 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

insert into whos_online (customer_id, full_name, session_id, ip_address, time_entry, time_last_click, last_page_url) values ('0', 'Guest', '8896376050473600371a47b9adc26011', '66.249.72.45', '1191128675', '1191128675', '/shop/product_info.php?cPath=986&products_id=14763?osCsid=336bba5788170dc35ebed03b4f5dca29')

 

# Sat Sep 29 23:06:04 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 18 Rows_examined: 82425

use datpcsto_pcstore;

select distinct(p.products_id) as p_id from products p , products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '2535' and p.products_status = '1' order by p.products_date_added desc limit 18

 

# Sat Sep 29 23:07:52 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

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

 

# Sat Sep 29 23:08:04 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

select r.reviews_id, left(rd.reviews_text, 100) as reviews_text, r.reviews_rating, r.date_added, r.customers_name from reviews r, reviews_description rd where r.products_id = '12296' and r.reviews_id = rd.reviews_id and rd.languages_id = '1' order by r.reviews_id desc LIMIT 0

 

# Sat Sep 29 23:08:07 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 7 Rows_examined: 1204

select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id

 

# Sat Sep 29 23:13:13 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 24 Rows_examined: 51450

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '530' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:15:34 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 29603

use datpcsto_pcstore;

SELECT p.products_image FROM products p, products_to_categories p2c WHERE p.products_id = p2c.products_id AND p2c.categories_id='967' and p.products_image!='' ORDER BY rand() LIMIT 1

 

# Sat Sep 29 23:16:05 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 25 Rows_examined: 54919

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '426' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:16:52 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 4 Rows_examined: 39870

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '543' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:17:31 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 14 Rows_examined: 45660

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '974' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:18:53 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 14 Rows_examined: 37672

use datpcsto_pcstore;

select distinct c.categories_id as id, cd.categories_name as name from products p, products_to_categories p2c, categories c, categories_description cd where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '1' and p.manufacturers_id = '849' order by cd.categories_name

 

# Sat Sep 29 23:19:36 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 25 Rows_examined: 240158

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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 = '53' order by p.products_model , pd.products_name limit 0, 25

 

# Sat Sep 29 23:20:05 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 29596

use datpcsto_pcstore;

select count(p.products_id) as total from products p

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '1128'

 

# Sat Sep 29 23:24:31 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 24 Rows_examined: 51450

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '274' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:24:32 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 29625

SELECT p.products_image FROM products p, products_to_categories p2c WHERE p.products_id = p2c.products_id AND p2c.categories_id='1698' and p.products_image!='' ORDER BY rand() LIMIT 1

 

# Sat Sep 29 23:24:56 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

delete from sessions where expiry < '1191129895'

 

# Sat Sep 29 23:29:26 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 25 Rows_examined: 96535

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '898' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:31:05 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 2 Rows_examined: 8

use datpcsto_pcstore;

select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = '195' and c.categories_id = cd.categories_id and cd.language_id='1' order by sort_order, cd.categories_name

 

# Sat Sep 29 23:33:26 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 5 Rows_examined: 40449

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '1760' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:37:55 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 37668

use datpcsto_pcstore;

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 = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '223' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10

 

# Sat Sep 29 23:38:12 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 38132

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '335' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:38:26 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

select p2c.categories_id from products p, products_to_categories p2c where p.products_id = '33907' and p.products_status = '1' and p.products_id = p2c.products_id limit 1

 

# Sat Sep 29 23:39:41 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 29607

use datpcsto_pcstore;

SELECT p.products_image FROM products p, products_to_categories p2c WHERE p.products_id = p2c.products_id AND p2c.categories_id='524' and p.products_image!='' ORDER BY rand() LIMIT 1

 

# Sat Sep 29 23:40:11 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

SELECT cache_id, cache_language_id, cache_name, cache_data, cache_global, cache_gzip, cache_method, cache_date, cache_expires FROM cache WHERE cache_language_id='1' AND cache_global='1'

 

# Sat Sep 29 23:41:20 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 38132

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '110' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:41:37 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 38132

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '171' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:41:54 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 2 Rows_examined: 38710

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '95' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:43:36 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

DELETE FROM cache WHERE cache_expires <= '2007-09-29 11:43:35'

 

# Sat Sep 29 23:44:07 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 37668

use datpcsto_pcstore;

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 = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '211' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10

 

# Sat Sep 29 23:44:47 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 73448

select count(p.products_id) as total from products p

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '535'

 

# Sat Sep 29 23:45:02 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 25 Rows_examined: 52631

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '936' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:45:08 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 7 Rows_examined: 1204

select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id

 

# Sat Sep 29 23:45:19 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 3 Rows_examined: 39288

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '140' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:45:36 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 11 Rows_examined: 43937

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '928' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:47:01 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

DELETE FROM cache WHERE cache_expires <= '2007-09-29 11:47:00'

 

# Sat Sep 29 23:47:18 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

DELETE FROM cache WHERE cache_expires <= '2007-09-29 11:47:17'

 

# Sat Sep 29 23:48:10 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 5 Rows_examined: 40499

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '910' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:48:24 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 25 Rows_examined: 108483

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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 = '59' order by final_price , pd.products_name limit 0, 25

 

# Sat Sep 29 23:48:28 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 37668

use datpcsto_pcstore;

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 = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '292' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10

 

# Sat Sep 29 23:48:44 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 5 Rows_examined: 40463

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '913' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:49:03 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 7 Rows_examined: 41607

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '1752' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:50:26 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

DELETE FROM cache WHERE cache_expires <= '2007-09-29 11:50:25'

 

# Sat Sep 29 23:51:18 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 37668

use datpcsto_pcstore;

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 = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '64' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10

 

# Sat Sep 29 23:51:52 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 2 Rows_examined: 38710

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '338' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:55:17 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 10 Rows_examined: 43344

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '545' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:55:34 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 23 Rows_examined: 51100

use datpcsto_pcstore;

select p.products_image, pd.products_name, p.products_model, 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

INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id

INNER JOIN products_description pd ON pd.products_id = p.products_id

left join manufacturers m on p.manufacturers_id = m.manufacturers_id

left join specials s on p.products_id = s.products_id

where p.products_status = '1' and p2c.categories_id = '556' order by pd.products_name limit 0, 25

 

# Sat Sep 29 23:56:36 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 29653

use datpcsto_pcstore;

SELECT p.products_image FROM products p, products_to_categories p2c WHERE p.products_id = p2c.products_id AND p2c.categories_id='2916' and p.products_image!='' ORDER BY rand() LIMIT 1

 

# Sat Sep 29 23:57:33 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

use datpcsto_pcstore;

SELECT cache_expires FROM cache WHERE cache_id='d7827cea38f84819405b201c72ff3db4' AND cache_language_id='1' LIMIT 1

 

# Sat Sep 29 23:59:37 2007

# Query_time: 1 Lock_time: 0 Rows_sent: 12 Rows_examined: 37857

use datpcsto_pcstore;

select distinct c.categories_id as id, cd.categories_name as name from products p, products_to_categories p2c, categories c, categories_description cd where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '1' and p.manufacturers_id = '173' order by cd.categories_name

Link to comment
Share on other sites

Today again 10 times My account exceeds CPU usage.

 

Is there a problem with this see below:

No, these are normal queries you can expect from osC and/or contributions. However, whatever changes and optimizations you apply to your shop (you haven't told us about anything you did to solve the problem...) it is obvious you have a large number of categories and products.

 

This will always lead to queries that have to examine a lot of rows, so taxing for the server. As was pointed out above regarding shared servers there is a price to pay for performance. From the looks of it your hosting company does not tell you that you use to many queries, but you have to many slow queries? Not much you can do about that IMO.

Link to comment
Share on other sites

do some speed optimization.

 

 

If dedicated SSL the set force cookies to tru e.This will allow You to use a cache categories.php in left column.This file uses too many resources.

 

Again change tax class chek in forum what needs to be changed i this.Probably class available with osc 3.0 will be helpful.

 

delete languages present but not in use.

 

Hope this makes some progress and ther are fewer timeouts.

also You can set in logging parse to true and display.Look in footer and You will see some parse time details.

 

Satish

Ask/Skype for Free osCommerce value addon/SEO suggestion tips for your site.

 

Check My About US For who am I and what My company does.

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...