Kindly help - Hosting suspension - Over utilization

[BathroomvanityCanada]

Can anyone help in this issue. We recently got a warning from our hosting company that our database on our hosting account has been disabled for violating Section 3 "Your Obligations" of Go Daddy's “Web Hosting and Virtual Dedicated Server Service Agreement.”

 

 

Problematic query:

select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, pd.products_description, p.products_price, p.products_tax_class_id, p2c.categories_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 manufacturers m using(manufacturers_id), products_description pd) left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c,products_attributes pa where p.products_status = '1' 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 order by pd.products_name limit 0, 20

 

EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE pd ALL PRIMARY 1251 Using where; Using temporary; Using filesort

1 SIMPLE p eq_ref PRIMARY PRIMARY 4 magickw_estore.pd.products_id 1 Using where

1 SIMPLE s ref idx_specials_products_id idx_specials_products_id 4 magickw_estore.pd.products_id 1

1 SIMPLE m eq_ref PRIMARY PRIMARY 4 magickw_estore.p.manufacturers_id 1 Using index

1 SIMPLE p2c ref PRIMARY PRIMARY 4 magickw_estore.p.products_id 1 Using where; Using index

1 SIMPLE pa index PRIMARY 4 1879 Using index; Distinct

1 SIMPLE c eq_ref PRIMARY PRIMARY 4 magickw_estore.p2c.categories_id 1 Using index; Distinct

 

This query examines 2350629 rows, which is unacceptable in shared hosting.

 

*** IMPORTANT ***

 

Kindly help us in solving this issue. I am not a programmer and hence unable to solve this.

 

Does this problem occur on search ? , how to solve this problem?

 

Would really be grateful for any help.

 

Thanks Again

[♥toyicebear]

Click me>>

[BathroomvanityCanada]

Hi Toyicebear,

Thanks a lot for your reply. I am now unable to login to my oscommerce admin page or can connect to my sql. How do i solve the problematic query issue?

 

Thank You

[♥toyicebear]

1. make a temporary html page "under maintenace will open again shortly" or somthing like that.

 

2. move all the oscommerce files into a sub directory with a difficult to guess name, modify the these 2 files with the new file paths includes/configure.php and admin/includes/configure.php

 

3. Contact you hosting and tell them what you have done and ask them to "lift" your suspension.

 

4. optimize the site which now is in the newly created sub-directory .. when you have it properly optimized then you can move it and make it live again.

 

NOTE: If you have extremely many categories/products you will probably have to move to a different hosting plan, virtual dedicated server or dedicated server.

 

Some quick pointers though...

 

1. Make sure "show category count" is set to false in the shop admin.

 

2. Make sure that "USE_PCONNECT" is set to false in both the configure.file

[BathroomvanityCanada]

Thanks a lot for your help toyicebear. Really appreciate it :)

[BathroomvanityCanada]

Hey Toyicebear, Just one more doubt. Do you have any idea on which page this query is? Is it under advanced search .php?

 

Thank You

[♥toyicebear]

Click me>>

[web-project]

This query examines 2350629 rows, which is unacceptable in shared hosting.

one issue can be that you are using too much server resources and they trying to push you to their VPS services.