ФорумРазработкаБазы данных → Оптимизация SQL запроса

Оптимизация SQL запроса

  • developer

    Сообщения: 241 Репутация: N Группа: Кто попало

    Spritz 23 июня 2015 г. 8:10

    Есть вот такая функция

    // *****************************************************************************
    // 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 параметров.
    Есть какие нибудь идеи для оптимизации этого пиздеца?

  • phpdude

    Сообщения: 26624 Репутация: N Группа: в ухо

    Spritz 23 июня 2015 г. 8:13, спустя 2 минуты 44 секунды

    ты реально полагаешь что кто-то бесплатно будет за это запариваться?))

    Спустя 9 сек.

    это не php.ru с задротами, это Пiха

    Сапожник без сапог
  • master

    Сообщения: 3236 Репутация: N Группа: Джедаи

    Spritz 23 июня 2015 г. 8:22, спустя 9 минут 12 секунд

    Что показывает explain на реальных данных?
    Правильно ли используются индексы?
    Если нет - то сначала возьмись за индексы, затем (если не поможет) проверь настройки выделенной памяти для СУБД, затем (если не поможет) - мониторь сервер на предмет что проседает - iops/cpu/etc, затем прикручивай кэш.

    хотя можешь сразу кэш прикрутить

    не всё полезно, что в swap полезло
  • technobulka

    Сообщения: 4540 Репутация: N Группа: Джедаи

    Spritz 23 июня 2015 г. 8:31, спустя 9 минут 1 секунду

    while ($_Row = db_fetch_assoc($Result)) {
    ...
    $q2 = db_query($sql);
    ...
    }

    При 130.000 товаров

    При частых вызовах этой функции

    Хм... Что же это может быть?..

    Высокоуровневое абстрактное говно
  • phpdude

    Сообщения: 26624 Репутация: N Группа: в ухо

    Spritz 23 июня 2015 г. 8:31, спустя 12 секунд

    @master, то что ты написал и так очевидно человеку который в курсе о чем идет речь. Полагаю это не тот случай, иначе топика бы не было )

    Сапожник без сапог
  • developer

    Сообщения: 241 Репутация: N Группа: Кто попало

    Spritz 23 июня 2015 г. 9:24, спустя 52 минуты 25 секунд

    ты реально полагаешь что кто-то бесплатно будет за это запариваться?))

    Честно, реально пытался найти человека который займется оптимизацией, никто не берется.
    Как максимум есть возможность, даже я бы сказал потребность, перейти на PHP 5.4 и выше, но SS Premium настолько старый, что как минимум там нужно перейти c mysql_pconnect и дальше по списку, чтобы движок завелся. Вот сколько оптимизация будет стоить и кто возьмется?

  • phpdude

    Сообщения: 26624 Репутация: N Группа: в ухо

    Spritz 23 июня 2015 г. 12:57, спустя 3 часа 33 минуты 27 секунд

    что как минимум там нужно перейти c mysql_pconnect

    @developer, да это бестолку в такой ситуации, это "спички". Структуру базы, ее наполнение, расстановку индексов надо смотреть.

    Хз, я оптимизацией занимался раньше от $400-500 за проект. Времени ибо много занимает, которое к тому же как правило еще и не прогнозируемо.

    Сапожник без сапог
  • kostyl

    Сообщения: 5203 Репутация: N Группа: Джедаи

    Spritz 23 июня 2015 г. 16:49, спустя 3 часа 52 минуты 13 секунд

    обычно, занимаются задачами которые приносят минимум сколько то денег

  • phpdude

    Сообщения: 26624 Репутация: N Группа: в ухо

    Spritz 23 июня 2015 г. 16:52, спустя 2 минуты 30 секунд

    @kostyl, или удовольствие, но удовольствие приносят мелкие вопросы, опессорц и свои няшки. Чужие задачи должны приносить деньги :)

    Сапожник без сапог
  • kostyl

    Сообщения: 5203 Репутация: N Группа: Джедаи

    Spritz 23 июня 2015 г. 16:54, спустя 2 минуты 32 секунды

    Чужие задачи должны приносить деньги :)

    @phpdude, однозначно, бесплатно работать - грех

  • artoodetoo

    Сообщения: 5139 Репутация: N Группа: в ухо

    Spritz 23 июня 2015 г. 22:33, спустя 5 часов 38 минут 21 секунду

    Сама по себе структура типа EAV довольно прожорлива, но врядли удастся от нее уйти. Можно попытаться что-то закешировать.
    Уверен, эти атрибуты товара меняются крайне редко, а читаются постоянно — значит кеш даст хороший профит.

    Самый очевидный метод: хранить в кеше сериализованную версию. И только если кеш пуст, обращаться к таблицам атрибутов и значений (и пополнить кеш).

    Могу затюнить это место разово за $500.

    ιιlllιlllι унц-унц
  • kostyl

    Сообщения: 5203 Репутация: N Группа: Джедаи

    Spritz 24 июня 2015 г. 3:28, спустя 4 часа 55 минут 6 секунд

    хранить в кеше

    @artoodetoo, в noSQL ?

  • artoodetoo

    Сообщения: 5139 Репутация: N Группа: в ухо

    Spritz 24 июня 2015 г. 4:24, спустя 55 минут 45 секунд

    в абстрактном кеше. это может быть та же база, только одной записью, а не размазано по атрибутам.
    позже можно подменить драйвер кеша на файлы/носкл/шаредмемори.

    ιιlllιlllι унц-унц
  • adw0rd

    Сообщения: 22905 Репутация: N Группа: в ухо

    Spritz 24 июня 2015 г. 4:27, спустя 3 минуты 42 секунды

    Я бы хранил в SphinxSearch, тот же кеш, но создан для полнотекстового поиска и фильтрации

    adw/0
  • Абырвалг

    Сообщения: 6476 Репутация: N Группа: Джедаи

    Spritz 24 июня 2015 г. 4:42, спустя 14 минут 27 секунд

    +1 за сфинкс, тем более в нем есть поддержка JSON. Вопрос лишь в том, насколько сложно кастомизировать движек под него и хватит ли навыков у топикстартера для этого... Как я понял - это какой-нить webasyst shop script или типа того.

Пожалуйста, авторизуйтесь, чтобы написать комментарий!