Mysql Speed

[Nintendude]

Hey Guys,

 

We have been having issues with our mysql server running slow and our hosting company suggests optimising code.

 

The slow querie log they sent me tends to have the same similar slow queries.

 

# Time: 091011 15:39:45

# User@Host:

# Query_time: 147 Lock_time: 0 Rows_sent: 6 Rows_examined: 2447

select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o, products p where opa.products_id = '15503' and opa.orders_id = opb.orders_id and opb.products_id != '15503' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit 6;

 

Any idea what this even means and any solutions on where I can find, and how to optimise the code that is causing these delays?

[♥ecartz]

./includes/modules/also_purchased_products.php:

    $orders_query = tep_db_query("select p.products_id, p.products_image from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p where opa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and opa.orders_id = opb.orders_id and opb.products_id != '" . (int)$HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);

[Nintendude]

./includes/modules/also_purchased_products.php:

    $orders_query = tep_db_query("select p.products_id, p.products_image from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p where opa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and opa.orders_id = opb.orders_id and opb.products_id != '" . (int)$HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);

 

Thanks any suggestions on how to change the code to maximize speed?

[Nintendude]

I've noticed that this also_purchased box in the products is pretty useless actually is there a way to disable it so it will not pull this module at all? And have it not show up in the product_info.php page?

 

Sorry if I sound stupid just not much of a coding troubleshooter.

[spax]

If you haven't yet, have a read through this thread:

 

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

[Nintendude]

Problem is we are using a much older version than the osc store. before the M2 i beleive.

[web-project]

We have been having issues with our mysql server running slow and our hosting company suggests optimising code.

what about optimising the mysql server/service it self? as some hosting company never invest money in security or optimisation servers...

[Jan Zonjee]

I've noticed that this also_purchased box in the products is pretty useless actually is there a way to disable it so it will not pull this module at all? And have it not show up in the product_info.php page?

In 2.2 RC2a this box (module_ is included on line 280-284. Below line 279-286 with commenting out the code that adds the also-purchased box.

<?php
/*    if ((USE_CACHE == 'true') && empty($SID)) {
     echo tep_cache_also_purchased(3600);
   } else {
     include(DIR_WS_MODULES . FILENAME_ALSO_PURCHASED_PRODUCTS);
   } */
 }
?>