Jump to content

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


Recommended Posts

Модуль версии 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

 

Модуль версии 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

×
×
  • Create New...