slowing down MySQL Server

[tritium]

I am havign a SERIOUS problem with OSCommerce ... one of my stores contains about 60,000 products, and the other about 200,000 products ... I am having issues with my service provider as there are long running queries that is bogging down the server, thus gaining complaints from other customers of my ISP ... here are some of the long-ones ...

 

select p.products_model, m.manufacturers_name, 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_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '130' order by pd.products_name limit 80, 20

 

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 '130' in (c.categories_id, c.parent_id) order by p.products_ordered desc, pd.products_name limit 10

 

select distinct 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, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '9' and p.products_status = '1' order by p.products_date_added desc limit 9

 

What are these?? How do I stop them or at least truncate them?? Help please

[The_ancient]

this are nessaccary Querys to the Stores Function, remove them you break the store

[The_ancient]

also it is not really the size of the query, bit the NUMBER OF TIME it is done

 

if you have a Large Number of Customers, and high Traffic maybe you should move up to a dedicated server

[tritium]

So EVERYONE has these same queries?? And everyone with these experiences the same slow downs?? I can't believe that ... there must be a way to change them or to restate them or remove what is unneccessary to allow the store to function

 

Dedicated server is the next step ... but steps have to be cost effective ... and right now, dedicated server is not cost-effective ... we don't have many customers yet (still designing but online) but we anticipate increased usage once officially launched ...

 

What do each one do? Where are they located?? I'm finding others on my own, but its taking a little while ...

 

For example, I have changed the following by commenting out what I feel is unnecessary ... store still functions and I see what I want to see ... do you see any problems arising?? All I want is, in this example, when a person clicks on a category, for the subcategories to be displayed, if there are any, and for the products to be displayed if there aren't any ...

 

$category_depth = 'top';

if (isset($cPath) && tep_not_null($cPath)) {

//$categories_products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");

//$cateqories_products = tep_db_fetch_array($categories_products_query);

//if ($cateqories_products['total'] > 0) {

//$category_depth = 'products'; // display products

//} else {

$category_parent_query = tep_db_query("select count(*) as total from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$current_category_id . "'");

$category_parent = tep_db_fetch_array($category_parent_query);

if ($category_parent['total'] > 0) {

$category_depth = 'nested'; // navigate through the categories

} else {

$category_depth = 'products'; // category has no products, but display the 'no products' message

}

}

// }

 

Your help is appreciated

[The_ancient]

if you dont have any customers and the serer is slowing down based oin those query, it is time to swtich ISP's

 

they are alreay overloaded their servers.

 

as afar as Changing or Limiting the Query, yes it is possible, you need would have to dictate what info needs to be pulled, not everyone needs all th Info osC provides

 

however Like I said I think you problems are bigger than a Few Extra Fields in the SQL query.....

[tritium]

OK - changing ISP is an option ... not one I look forward to, since the SSL certificate was purchased through them, though it is a geotrust cert ... but the timing might be right since our subscription is coming due beginning of August ...

 

In the meantime, what are those first queries trying to call anyways?? In what file can I find them?? Knowing that, I coudl figure out if I need all the info being provided or not ...

[The_ancient]

lloks like those are for the product_info page