Optimize MySQL queries of Modification

[nester at zpi]

Sometime ago, I had a mod written by a freelancer, and it works quite well, but I have recently implemented it in a store with much higher traffic. With this new implementation I've found that the queries are unbearably slow. I can move the old index.php back into place, and my load average drops dramatically.. I'm curious if anyone could offer any suggestions on changes I could do to make this somewhat less load.

 

For what it's worth, the mod allows customers to search products by make/model/year of their vehicle. If they do not specify a vehicle, they should see all products. If they select a vehicle, they should see all products tied to their vehicle, as well as any product with no vehicles attached to it (IE: it's universal).

 

This works, just slowly. I think it may be due to implementation or query.

 

Here's the code from index.php

 

	//cars addon bof
$car_from = "";
$car_where = "";

//if ($_SESSION['cmake'])
$car_from .= " left join ".TABLE_CARS_TO_PRODUCTS." c2p on c2p.products_id = p.products_id ";

if ((int)@$_SESSION['cmake']) $car_where .= " and (c2p.cars_makes_id = ".(int)@$_SESSION['cmake']." or c2p.cars_makes_id is null) ";
if ((int)@$_SESSION['cmodel']) $car_where .= " and (c2p.cars_models_id = ".(int)@$_SESSION['cmodel']." or c2p.cars_models_id is null) ";
if ((int)@$_SESSION['cyear']) $car_where .= " and (c2p.cars_years_id = ".(int)@$_SESSION['cyear']." or c2p.cars_years_id is null) ";

//cars addon eof

// show the products of a specified manufacturer
if (isset($HTTP_GET_VARS['manufacturers_id'])) {

if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
// $listing_sql = "select " . $select_column_list . " 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 (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ) left join " . TABLE_SPECIALS . " s on
p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p
2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'"; //

$listing_sql = "select " . $select_column_list . " 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_ne
w_products_price, p.products_price) as final_price from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ) left join " . TABLE_SPECIALS . " s on p.p
roducts_id = s.products_id " . $car_from . " where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.
products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'" . $car_where;

} else {

// We show them all
$listing_sql = "select " . $select_column_list . " 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_ne
w_products_price, p.products_price) as final_price from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id " . $car_from .
" where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'"
. $car_where;

}

} else {
// show the products in a given categorie
if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {

// We are asked to show only specific catgeory
$listing_sql = "select " . $select_column_list . " 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_ne
w_products_price, p.products_price) as final_price from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join " . TABLE_SPECIALS . " s on p.pr
oducts_id = s.products_id " . $car_from . " where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products
_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'" . $car_where;
} else {

// We show them all
$listing_sql = "select " . $select_column_list . " 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_ne
w_products_price, p.products_price) as final_price from ((" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p) left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORI
ES . " p2c) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id " . $car_from . " where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$lang
uages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'" . $car_where;
}
}

  $listing_sql .= " group by p.products_id";

 

Any suggestions?

[boostin4hp]

Sometime ago, I had a mod written by a freelancer, and it works quite well, but I have recently implemented it in a store with much higher traffic. With this new implementation I've found that the queries are unbearably slow. I can move the old index.php back into place, and my load average drops dramatically.. I'm curious if anyone could offer any suggestions on changes I could do to make this somewhat less load.

 

For what it's worth, the mod allows customers to search products by make/model/year of their vehicle. If they do not specify a vehicle, they should see all products. If they select a vehicle, they should see all products tied to their vehicle, as well as any product with no vehicles attached to it (IE: it's universal).

 

This works, just slowly. I think it may be due to implementation or query.

 

Here's the code from index.php

 

	//cars addon bof
$car_from = "";
$car_where = "";

//if ($_SESSION['cmake'])
$car_from .= " left join ".TABLE_CARS_TO_PRODUCTS." c2p on c2p.products_id = p.products_id ";

if ((int)@$_SESSION['cmake']) $car_where .= " and (c2p.cars_makes_id = ".(int)@$_SESSION['cmake']." or c2p.cars_makes_id is null) ";
if ((int)@$_SESSION['cmodel']) $car_where .= " and (c2p.cars_models_id = ".(int)@$_SESSION['cmodel']." or c2p.cars_models_id is null) ";
if ((int)@$_SESSION['cyear']) $car_where .= " and (c2p.cars_years_id = ".(int)@$_SESSION['cyear']." or c2p.cars_years_id is null) ";

//cars addon eof

// show the products of a specified manufacturer
if (isset($HTTP_GET_VARS['manufacturers_id'])) {

if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
// $listing_sql = "select " . $select_column_list . " 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 (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ) left join " . TABLE_SPECIALS . " s on
p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p
2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'"; //

$listing_sql = "select " . $select_column_list . " 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_ne
w_products_price, p.products_price) as final_price from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ) left join " . TABLE_SPECIALS . " s on p.p
roducts_id = s.products_id " . $car_from . " where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.
products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'" . $car_where;

} else {

// We show them all
$listing_sql = "select " . $select_column_list . " 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_ne
w_products_price, p.products_price) as final_price from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id " . $car_from .
" where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'"
. $car_where;

}

} else {
// show the products in a given categorie
if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {

// We are asked to show only specific catgeory
$listing_sql = "select " . $select_column_list . " 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_ne
w_products_price, p.products_price) as final_price from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join " . TABLE_SPECIALS . " s on p.pr
oducts_id = s.products_id " . $car_from . " where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products
_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'" . $car_where;
} else {

// We show them all
$listing_sql = "select " . $select_column_list . " 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_ne
w_products_price, p.products_price) as final_price from ((" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p) left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORI
ES . " p2c) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id " . $car_from . " where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$lang
uages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'" . $car_where;
}
}

  $listing_sql .= " group by p.products_id";

 

Any suggestions?

 

 

Nester,

 

I have what seems to be the same contrib as you but I don't experience the slow loading times you describe, your index.php looks pretty much the same as mine so the problem most likely is in your categories.php box or the make model year box. Post both of those and I'll compare them to mine.

 

-Jeff