Jump to content

Модуль многоуровневой навигации для версии 1.4.11 грузит сильно базу. Вплоть до выпадания сайта

lightman
 Share

Recommended Posts

lightman Newbie

lightman

Posted
Posted

Модуль версии 1.9.1

 

что можно сделать?

Запросами которыми перегружается база:

  1. SET timestamp=1409984287;
    SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name
    FROM `ps_category_product` cp
    INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)
    INNER JOIN ps_product p ON (p.id_product = cp.id_product)
    INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
    INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4
    AND psi.price_min <= 288000 AND psi.price_max >= 176)
    WHERE
    c.nleft >= 5
    AND c.nright <= 6
    AND c.active = 1
    AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049)
    GROUP BY p.id_manufacturer;

    SET timestamp=1409984285;
    SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,
    COUNT(DISTINCT p.id_product) nbr,
    lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name
    FROM ps_feature_product fp
    INNER JOIN ps_product p ON (p.id_product = fp.id_product)
    LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)
    INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))
    LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)
    LEFT JOIN ps_layered_indexable_feature_lang_value lifl
    ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)
    LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl
    ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)
    INNER JOIN `ps_layered...
    SET timestamp=1409984285;
    SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,
    COUNT(DISTINCT p.id_product) nbr,
    lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name
    FROM ps_feature_product fp
    INNER JOIN ps_product p ON (p.id_product = fp.id_product)
    LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)
    INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))
    LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)
    LEFT JOIN ps_layered_indexable_feature_lang_value lifl
    ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)
    LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl
    ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)
    INNER JOIN `ps_layered...
    SET timestamp=1409984284;
    SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,
    COUNT(DISTINCT p.id_product) nbr,
    lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name
    FROM ps_feature_product fp
    INNER JOIN ps_product p ON (p.id_product = fp.id_product)
    LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)
    INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))
    LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)
    LEFT JOIN ps_layered_indexable_feature_lang_value lifl
    ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)
    LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl
    ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)
    INNER JOIN `ps_layered...
    SET timestamp=1409984284;
    SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,
    COUNT(DISTINCT p.id_product) nbr,
    lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name
    FROM ps_feature_product fp
    INNER JOIN ps_product p ON (p.id_product = fp.id_product)
    LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)
    INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))
    LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)
    LEFT JOIN ps_layered_indexable_feature_lang_value lifl
    ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)
    LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl
    ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)
    INNER JOIN `ps_layered...
    SET timestamp=1409984277;
    SELECT p.`id_product` id_product
    FROM `ps_product` p
    INNER JOIN `ps_layered_price_index` psi
    ON
    (
    psi.price_min >= 176
    AND psi.price_max <= 288000
    AND psi.`id_product` = p.`id_product`
    AND psi.`id_currency` = 4
    )
    INNER JOIN ps_category_product cp
    ON p.id_product = cp.id_product
    INNER JOIN ps_category c ON (c.id_category = cp.id_category AND
    c.nleft >= 5
    AND c.nright <= 6
    AND c.active = 1)
    WHERE 1 AND p.`active` = 1 AND p.id_product IN (SELECT `id_product` FROM `ps_feature_product` fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050) GROUP BY id_product;

    SET timestamp=1409984274;
    SELECT p.`id_product` id_product
    FROM `ps_product` p
    INNER JOIN `ps_layered_price_index` psi
    ON
    ((psi.price_min < 176 AND psi.price_max > 176)
    OR
    (psi.price_max > 288000 AND psi.price_min < 288000))
    AND psi.`id_product` = p.`id_product`
    AND psi.`id_currency` = 4
    INNER JOIN ps_category_product cp
    ON p.id_product = cp.id_product
    INNER JOIN ps_category c ON (c.id_category = cp.id_category AND
    c.nleft >= 5
    AND c.nright <= 6
    AND c.active = 1)
    WHERE 1 AND p.`active` = 1 AND p.id_product IN (SELECT `id_product` FROM `ps_feature_product` fp WHERE fp.`id_feature_value` = 217049) GROUP BY id_product;

    SET timestamp=1409984269;
    SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name
    FROM `ps_category_product` cp
    INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)
    INNER JOIN ps_product p ON (p.id_product = cp.id_product)
    INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
    INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4
    AND psi.price_min <= 61038 AND psi.price_max >= 3335)
    WHERE
    c.nleft >= 3
    AND c.nright <= 4
    AND c.active = 1
    AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050)
    GROUP BY p.id_manufacturer;

    SET timestamp=1409984268;
    SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name
    FROM `ps_category_product` cp
    INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)
    INNER JOIN ps_product p ON (p.id_product = cp.id_product)
    INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
    INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4
    AND psi.price_min <= 61038 AND psi.price_max >= 3335)
    WHERE
    c.nleft >= 3
    AND c.nright <= 4
    AND c.active = 1
    AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050)
    GROUP BY p.id_manufacturer;

    SET timestamp=1409984268;
    SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name
    FROM `ps_category_product` cp
    INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)
    INNER JOIN ps_product p ON (p.id_product = cp.id_product)
    INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
    INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4
    AND psi.price_min <= 61038 AND psi.price_max >= 3335)
    WHERE
    c.nleft >= 3
    AND c.nright <= 4
    AND c.active = 1
    AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050)
    GROUP BY p.id_manufacturer; 

 

Link to comment
Share on other sites
absent Newbie

absent

Posted
Posted

 

Модуль версии 1.9.1

 

что можно сделать?

Запросами которыми перегружается база:

  1. SET timestamp=1409984287;

    SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name

    FROM `ps_category_product` cp

    INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)

    INNER JOIN ps_product p ON (p.id_product = cp.id_product)

    INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)

    INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4

    AND psi.price_min <= 288000 AND psi.price_max >= 176)

    WHERE

    c.nleft >= 5

    AND c.nright <= 6

    AND c.active = 1

    AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049)

    GROUP BY p.id_manufacturer;

     

    SET timestamp=1409984285;

    SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,

    COUNT(DISTINCT p.id_product) nbr,

    lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name

    FROM ps_feature_product fp

    INNER JOIN ps_product p ON (p.id_product = fp.id_product)

    LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)

    INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))

    LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)

    LEFT JOIN ps_layered_indexable_feature_lang_value lifl

    ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)

    LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl

    ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)

    INNER JOIN `ps_layered...

    SET timestamp=1409984285;

    SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,

    COUNT(DISTINCT p.id_product) nbr,

    lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name

    FROM ps_feature_product fp

    INNER JOIN ps_product p ON (p.id_product = fp.id_product)

    LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)

    INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))

    LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)

    LEFT JOIN ps_layered_indexable_feature_lang_value lifl

    ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)

    LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl

    ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)

    INNER JOIN `ps_layered...

    SET timestamp=1409984284;

    SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,

    COUNT(DISTINCT p.id_product) nbr,

    lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name

    FROM ps_feature_product fp

    INNER JOIN ps_product p ON (p.id_product = fp.id_product)

    LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)

    INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))

    LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)

    LEFT JOIN ps_layered_indexable_feature_lang_value lifl

    ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)

    LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl

    ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)

    INNER JOIN `ps_layered...

    SET timestamp=1409984284;

    SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,

    COUNT(DISTINCT p.id_product) nbr,

    lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name

    FROM ps_feature_product fp

    INNER JOIN ps_product p ON (p.id_product = fp.id_product)

    LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)

    INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))

    LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)

    LEFT JOIN ps_layered_indexable_feature_lang_value lifl

    ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)

    LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl

    ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)

    INNER JOIN `ps_layered...

    SET timestamp=1409984277;

    SELECT p.`id_product` id_product

    FROM `ps_product` p

    INNER JOIN `ps_layered_price_index` psi

    ON

    (

    psi.price_min >= 176

    AND psi.price_max <= 288000

    AND psi.`id_product` = p.`id_product`

    AND psi.`id_currency` = 4

    )

    INNER JOIN ps_category_product cp

    ON p.id_product = cp.id_product

    INNER JOIN ps_category c ON (c.id_category = cp.id_category AND

    c.nleft >= 5

    AND c.nright <= 6

    AND c.active = 1)

    WHERE 1 AND p.`active` = 1 AND p.id_product IN (SELECT `id_product` FROM `ps_feature_product` fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050) GROUP BY id_product;

     

    SET timestamp=1409984274;

    SELECT p.`id_product` id_product

    FROM `ps_product` p

    INNER JOIN `ps_layered_price_index` psi

    ON

    ((psi.price_min < 176 AND psi.price_max > 176)

    OR

    (psi.price_max > 288000 AND psi.price_min < 288000))

    AND psi.`id_product` = p.`id_product`

    AND psi.`id_currency` = 4

    INNER JOIN ps_category_product cp

    ON p.id_product = cp.id_product

    INNER JOIN ps_category c ON (c.id_category = cp.id_category AND

    c.nleft >= 5

    AND c.nright <= 6

    AND c.active = 1)

    WHERE 1 AND p.`active` = 1 AND p.id_product IN (SELECT `id_product` FROM `ps_feature_product` fp WHERE fp.`id_feature_value` = 217049) GROUP BY id_product;

     

    SET timestamp=1409984269;

    SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name

    FROM `ps_category_product` cp

    INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)

    INNER JOIN ps_product p ON (p.id_product = cp.id_product)

    INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)

    INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4

    AND psi.price_min <= 61038 AND psi.price_max >= 3335)

    WHERE

    c.nleft >= 3

    AND c.nright <= 4

    AND c.active = 1

    AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050)

    GROUP BY p.id_manufacturer;

     

    SET timestamp=1409984268;

    SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name

    FROM `ps_category_product` cp

    INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)

    INNER JOIN ps_product p ON (p.id_product = cp.id_product)

    INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)

    INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4

    AND psi.price_min <= 61038 AND psi.price_max >= 3335)

    WHERE

    c.nleft >= 3

    AND c.nright <= 4

    AND c.active = 1

    AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050)

    GROUP BY p.id_manufacturer;

     

    SET timestamp=1409984268;

    SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name

    FROM `ps_category_product` cp

    INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)

    INNER JOIN ps_product p ON (p.id_product = cp.id_product)

    INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)

    INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4

    AND psi.price_min <= 61038 AND psi.price_max >= 3335)

    WHERE

    c.nleft >= 3

    AND c.nright <= 4

    AND c.active = 1

    AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050)

    GROUP BY p.id_manufacturer; 

 

скайп глянь

Link to comment
Share on other sites
 Share
Go to topic listing
×
×
  • Create New...