Есть вот такая функция
// *****************************************************************************
// Purpose gets extra parametrs
// Inputs $productID - product ID
// Remarks
// Returns array of value extraparametrs
// each item of this array has next struture
// first type "option_type" = 0
// "name" - parametr name
// "option_value" - value
// "option_type" - 0
// second type "option_type" = 1
// "name" - parametr name
// "option_show_times" - how times does show in client side this
// parametr to select
// "variantID_default" - variant ID by default
// "values_to_select" - array of variant value to select
// each item of "values_to_select" array has next structure
// "variantID" - variant ID
// "price_surplus" - to added to price
// "option_value" - value
function GetExtraParametrs($productID)
{
if (!is_array($productID)) {
$ProductIDs = array($productID);
$IsProducts = false;
} elseif (count($productID)) {
$ProductIDs = & $productID;
$IsProducts = true;
} else {
return array();
}
$ProductsExtras = array();
$sql = '
SELECT povt.productID,pot.optionID,pot.name,povt.option_value,povt.option_type,povt.option_show_times, povt.variantID, povt.optionID
FROM ?#PRODUCT_OPTIONS_VALUES_TABLE as povt LEFT JOIN ?#PRODUCT_OPTIONS_TABLE as pot ON pot.optionID=povt.optionID
WHERE povt.productID IN ([email protected]) ORDER BY pot.sort_order, pot.name
';
$Result = db_phquery($sql, $ProductIDs);
while ($_Row = db_fetch_assoc($Result)) {
$_Row;
$b = null;
if (($_Row['option_type'] == 0 || $_Row['option_type'] == NULL) && strlen(trim($_Row['option_value'])) > 0) {
$ProductsExtras[$_Row['productID']][] = array(
'option_type' => $_Row['option_type'],
'name' => $_Row['name'],
'option_value' => $_Row['option_value']
);
} /**
* @features "Extra options values"
* @state begin
*/
else if ($_Row['option_type'] == 1) {
//fetch all option values variants
$sql = '
SELECT povvt.option_value, povvt.variantID, post.price_surplus
FROM ' . PRODUCTS_OPTIONS_SET_TABLE . ' as post
LEFT JOIN ' . PRODUCTS_OPTIONS_VALUES_VARIANTS_TABLE . ' as povvt
ON povvt.variantID=post.variantID
WHERE povvt.optionID=' . $_Row['optionID'] . ' AND post.productID=' . $_Row['productID'] . ' AND povvt.optionID=' . $_Row['optionID'] . '
ORDER BY povvt.sort_order, povvt.option_value
';
$q2 = db_query($sql);
$_Row['values_to_select'] = array();
$i = 0;
while ($_Rowue = db_fetch_assoc($q2)) {
$_Row['values_to_select'][$i] = array();
$_Row['values_to_select'][$i]['option_value'] = $_Rowue['option_value'];
if ($_Rowue['price_surplus'] > 0) $_Row['values_to_select'][$i]['option_value'] .= ' (+ ' . show_price($_Rowue['price_surplus']) . ')';
elseif ($_Rowue['price_surplus'] < 0) $_Row['values_to_select'][$i]['option_value'] .= ' (- ' . show_price(-$_Rowue['price_surplus']) . ')';
$_Row['values_to_select'][$i]['option_valueWithOutPrice'] = $_Rowue['option_value'];
$_Row['values_to_select'][$i]['price_surplus'] = show_priceWithOutUnit($_Rowue['price_surplus']);
$_Row['values_to_select'][$i]['variantID'] = $_Rowue['variantID'];
$i++;
}
$ProductsExtras[$_Row['productID']][] = $_Row;
}
/**
* @features "Extra options values"
* @state end
*/
}
if (!$IsProducts) {
if (!count($ProductsExtras)) return array();
else {
return $ProductsExtras[$productID];
}
}
return $ProductsExtras;
}Она формирует такой запрос:
SELECT povt.productID,pot.optionID,pot.name,povt.option_value,povt.option_type,povt.option_show_times, povt.variantID, povt.optionID
FROM ss_product_options_values as povt LEFT JOIN ss_product_options as pot ON pot.optionID=povt.optionID
WHERE povt.productID IN ('96953') ORDER BY pot.sort_order, pot.nameС вот такой структурой базы:
--
-- Структура таблицы `ss_product_options`
--
CREATE TABLE IF NOT EXISTS `ss_product_options` (
`optionID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sort_order` int(11) DEFAULT '0',
PRIMARY KEY (`optionID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=19 ;
-- --------------------------------------------------------
--
-- Структура таблицы `ss_product_options_set`
--
CREATE TABLE IF NOT EXISTS `ss_product_options_set` (
`productID` int(11) NOT NULL DEFAULT '0',
`optionID` int(11) NOT NULL DEFAULT '0',
`variantID` int(11) NOT NULL DEFAULT '0',
`price_surplus` float DEFAULT '0',
PRIMARY KEY (`productID`,`optionID`,`variantID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
-- --------------------------------------------------------
--
-- Структура таблицы `ss_product_options_values`
--
CREATE TABLE IF NOT EXISTS `ss_product_options_values` (
`optionID` int(11) NOT NULL DEFAULT '0',
`productID` int(11) NOT NULL DEFAULT '0',
`option_value` varchar(255) DEFAULT NULL,
`option_type` tinyint(1) DEFAULT '0',
`option_show_times` int(11) DEFAULT '1',
`variantID` int(11) DEFAULT NULL,
PRIMARY KEY (`optionID`,`productID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;При частых вызовах этой функции, Shop-Script Premium просто гаснет вместе с сервером.
При 130.000 товаров и у каждого порядка 5-10 параметров.
Есть какие нибудь идеи для оптимизации этого пиздеца?
