chargement entre chaque categories trop longue

[kate66]

Bonjour,

sur un prestashop 1.7.7.0  avec thème acheté.

Le site est anormalement entre chaque catégorie mais quelques fois dans la journée sinon c'est intempestives.

https://hkboutik.fr/

Après de multiples test sur un decouvert que cela venait de category.php sur un desactivers des modules qui aurait pu provoquer cela mais rien a faire.

aurirez vous une idée et sur utiliser cela :

 

mais cela n'a rien changé pour nous.

Auriez-vous d'autres suggestions? MERCI

Voici le rapport de profilage de débogage

Edited April 22, 2021 by kate66 (see edit history)

[Eolia]

Sur quelle catégorie êtes-vous pour renvoyer plus de 3000 résultats ?

Quand je navigue sur votre site les temps de réponse sont corrects:

[kate66]

bonjour, j'etais sur l'index.

Je sais le problème est vraiement intempestive la il fonctionne il y'a une heure non et le bug reviens 2 à 3 fois dans la journée à des heures différentes.L'hebergeur nous indique que des requetes avec de jointures sont trop grosse ? Merci

[Eolia]

Donc le souci doit arriver lorsque que ça recompile (css, tpl ou js modifiés)

Si l'hébergeur peut vous donner quelles sont les requêtes concernées, ça pourrait aider^^

[kate66]

je pense que c'est cela: 

SELECT SQL_NO_CACHE * FROM `ps_product` a LEFT JOIN `ps_product_lang` `b` ON a.`id_product` = b.`id_product` AND b.`id_lang` = 1 LEFT JOIN `ps_product_shop` `c` ON a.`id_product` = c.`id_product` AND c.`id_shop` = 1 WHERE (a.`id_product` = 2474) AND (b.`id_shop` = 1) LIMIT 1

1980.31/src/Adapter/EntityMapper.php:75

SELECT SQL_NO_CACHE image_shop.`id_image` FROM `ps_image` i INNER JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1) WHERE i.`id_product` = 2112 AND image_shop.`cover` = 1 LIMIT 1

19745/classes/Product.php:3118

SELECT SQL_NO_CACHE bgm.*, s.`name` , bgml.`content` FROM ps_bongooglemap bgm LEFT JOIN ps_store_lang s ON(bgm.`id_store` = s.`id_store`) LEFT JOIN ps_bongooglemap_lang bgml ON(bgm.`id_tab` = bgml.`id_tab`) WHERE bgm.`id_shop` = 1 AND bgm.`id_shop` = 1 AND bgml.`id_lang` = 1 AND s.`id_lang` = 1 AND bgm.`status` = 1 ORDER BY bgm.`id_tab`

19131Yes/modules/bongooglemap/classes/ClassGooglemap.php:106

SELECT SQL_NO_CACHE p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity, IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image, il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB("2021-04-21 00:00:00", INTERVAL 65 DAY)) > 0 AS new, product_shop.price AS orderprice FROM `ps_category_product` cp LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1) LEFT JOIN ps_stock_available stock ON (stock.id_product = `p`.id_product AND stock.id_product_attribute = 0 AND stock.id_shop = 1 AND stock.id_shop_group = 0 ) LEFT JOIN `ps_category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1 ) LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1 AND pl.id_shop = 1 ) LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=1) LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1) LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = 1 AND cp.`id_category` = 2 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ORDER BY RAND() LIMIT 12

1904.43376Yes/classes/Category.php:1056

SELECT SQL_NO_CACHE cp.`id_product_attribute`, cp.`id_product`, cp.`quantity` AS cart_quantity, cp.id_shop, cp.`id_customization`, pl.`name`, p.`is_virtual`, pl.`description_short`, pl.`available_now`, pl.`available_later`, product_shop.`id_category_default`, p.`id_supplier`, p.`id_manufacturer`, m.`name` AS manufacturer_name, product_shop.`on_sale`, product_shop.`ecotax`, product_shop.`additional_shipping_cost`, product_shop.`available_for_order`, product_shop.`show_price`, product_shop.`price`, product_shop.`active`, product_shop.`unity`, product_shop.`unit_price_ratio`, stock.`quantity` AS quantity_available, p.`width`, p.`height`, p.`depth`, stock.`out_of_stock`, p.`weight`, p.`available_date`, p.`date_add`, p.`date_upd`, IFNULL(stock.quantity, 0) as quantity, pl.`link_rewrite`, cl.`link_rewrite` AS category, CONCAT(LPAD(cp.`id_product`, 10, 0), LPAD(IFNULL(cp.`id_product_attribute`, 0), 10, 0), IFNULL(cp.`id_address_delivery`, 0), IFNULL(cp.`id_customization`, 0)) AS unique_id, cp.id_address_delivery, product_shop.advanced_stock_management, ps.product_supplier_reference supplier_reference, cu.`id_customization`, cu.`quantity` AS customization_quantity, product_attribute_shop.`price` AS price_attribute, product_attribute_shop.`ecotax` AS ecotax_attr, IF (IFNULL(pa.`reference`, '') = '', p.`reference`, pa.`reference`) AS reference, (p.`weight`+ pa.`weight`) weight_attribute, IF (IFNULL(pa.`ean13`, '') = '', p.`ean13`, pa.`ean13`) AS ean13, IF (IFNULL(pa.`isbn`, '') = '', p.`isbn`, pa.`isbn`) AS isbn, IF (IFNULL(pa.`upc`, '') = '', p.`upc`, pa.`upc`) AS upc, IF (IFNULL(pa.`mpn`, '') = '', p.`mpn`, pa.`mpn`) AS mpn, IFNULL(product_attribute_shop.`minimal_quantity`, product_shop.`minimal_quantity`) as minimal_quantity, IF(product_attribute_shop.wholesale_price > 0, product_attribute_shop.wholesale_price, product_shop.`wholesale_price`) wholesale_price , image_shop.`id_image` id_image, il.`legend` FROM `ps_cart_product` cp LEFT JOIN `ps_product` `p` ON p.`id_product` = cp.`id_product` INNER JOIN `ps_product_shop` `product_shop` ON (product_shop.`id_shop` = cp.`id_shop` AND product_shop.`id_product` = p.`id_product`) LEFT JOIN `ps_product_lang` `pl` ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1 AND pl.id_shop = cp.id_shop LEFT JOIN `ps_category_lang` `cl` ON product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = cp.id_shop LEFT JOIN `ps_product_supplier` `ps` ON ps.`id_product` = cp.`id_product` AND ps.`id_product_attribute` = cp.`id_product_attribute` AND ps.`id_supplier` = p.`id_supplier` LEFT JOIN `ps_manufacturer` `m` ON m.`id_manufacturer` = p.`id_manufacturer` LEFT JOIN ps_stock_available stock ON (stock.id_product = `cp`.id_product AND stock.id_product_attribute = IFNULL(`cp`.id_product_attribute, 0) AND stock.id_shop = 1 AND stock.id_shop_group = 0 ) LEFT JOIN `ps_customization` `cu` ON p.`id_product` = cu.`id_product` AND cp.`id_product_attribute` = cu.`id_product_attribute` AND cp.`id_customization` = cu.`id_customization` AND cu.`id_cart` = 25099 LEFT JOIN `ps_product_attribute` `pa` ON pa.`id_product_attribute` = cp.`id_product_attribute` LEFT JOIN `ps_product_attribute_shop` `product_attribute_shop` ON (product_attribute_shop.`id_shop` = cp.`id_shop` AND product_attribute_shop.`id_product_attribute` = pa.`id_product_attribute`) LEFT JOIN `ps_image_shop` `image_shop` ON image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=1 LEFT JOIN `ps_image_lang` `il` ON il.`id_image` = image_shop.`id_image` AND il.`id_lang` = 1 WHERE (cp.`id_cart` = 25099) AND (p.`id_product` IS NOT NULL) GROUP BY cp.`id_product_attribute`, cp.`id_product`, cp.`id_shop`, cp.`id_customization` ORDER BY cp.`date_add`, cp.`id_product`, cp.`id_product_attribute` ASC

1719.924YesYes/classes/Cart.php:760

SELECT SQL_NO_CACHE cr.* FROM ps_cart_rule cr LEFT JOIN ps_cart_rule_shop crs ON cr.id_cart_rule = crs.id_cart_rule LEFT JOIN ps_cart_rule_carrier crca ON cr.id_cart_rule = crca.id_cart_rule LEFT JOIN ps_carrier c ON (c.id_reference = crca.id_carrier AND c.deleted = 0) LEFT JOIN ps_cart_rule_country crco ON cr.id_cart_rule = crco.id_cart_rule WHERE cr.active = 1 AND cr.code = "" AND cr.quantity > 0 AND NOW() BETWEEN cr.date_from AND cr.date_to AND ( cr.id_customer = 0 OR cr.id_customer = 2 ) AND ( cr.`carrier_restriction` = 0 OR c.id_carrier = 15 ) AND ( cr.`shop_restriction` = 0 ) AND ( cr.`group_restriction` = 0 OR EXISTS ( SELECT 1 FROM `ps_customer_group` cg INNER JOIN `ps_cart_rule_group` crg ON cg.id_group = crg.id_group WHERE cr.`id_cart_rule` = crg.`id_cart_rule` AND cg.`id_customer` = 2 LIMIT 1 ) ) AND ( cr.`reduction_product` <= 0 OR EXISTS ( SELECT 1 FROM `ps_cart_product` WHERE `ps_cart_product`.`id_product` = cr.`reduction_product` AND `id_cart` = 25099 ) ) AND NOT EXISTS (SELECT 1 FROM ps_cart_cart_rule WHERE cr.id_cart_rule = ps_cart_cart_rule.id_cart_rule AND id_cart = 25099) ORDER BY priority

1703.4148Yes/classes/CartRule.php:1629

SELECT SQL_NO_CACHE COUNT(DISTINCT `id_product`) FROM `ps_specific_price` WHERE `id_product` != 0 LIMIT 1

1693.8598/classes/SpecificPrice.php:286

SELECT SQL_NO_CACHE pa.`available_date` FROM `ps_product` p LEFT JOIN `ps_product_attribute` pa ON (pa.`id_product` = p.`id_product`) INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) INNER JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1) WHERE p.`id_product` = 1644 AND pa.`id_product` = 1644 AND pa.`id_product_attribute` = 5672 LIMIT 1

1629.81/classes/Product.php:949

SELECT SQL_NO_CACHE product_shop.`price`, product_shop.`ecotax`, IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute, product_attribute_shop.`price` AS attribute_price, product_attribute_shop.default_on FROM `ps_product` p INNER JOIN `ps_product_shop` `product_shop` ON (product_shop.id_product=p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_product_attribute_shop` `product_attribute_shop` ON (product_attribute_shop.id_product = p.id_product AND product_attribute_shop.id_shop = 1) WHERE (p.`id_product` = 2410)

16129/classes/Product.php:3401

SELECT SQL_NO_CACHE m.`id_module`, m.`name`, ms.`id_module`as `mshop` FROM `ps_module` m LEFT JOIN `ps_module_shop` ms ON m.`id_module` = ms.`id_module` AND ms.`id_shop` = 1

1611.2118/classes/module/Module.php:322

SELECT SQL_NO_CACHE image_shop.`cover`, i.`id_image`, il.`legend`, i.`position` FROM `ps_image` i INNER JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1) LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1) WHERE i.`id_product` = 2457 ORDER BY `position`

[kate66]

ce sont les 1 qui s'afficher avec le debug profiling

[kate66]

l'herbergeur nous a expliquer trop de jointure je crois

 

[Eolia]

il y a 5 minutes, kate66 a dit :

SELECT SQL_NO_CACHE image_shop.`id_image` FROM `ps_image` i INNER JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1) WHERE i.`id_product` = 2112 AND image_shop.`cover` = 1 LIMIT 1

19745/classes/Product.php:3118

Y a un souci serveur si cette requête, assez simple et qui ne doit renvoyer qu'un seul résultat, met 19 secondes à répondre...

[Eolia]

il y a 1 minute, kate66 a dit :

l'herbergeur nous a expliquer trop de jointure je crois

 

Rien à voir, plutôt une configuration MySQL moisie. D'ailleurs ce ne sont pas les plus grosses requêtes qui mettent le plus de temps à répondre.

[kate66]

ah bon on a MySQL 5.7

[kate66]

J'ai activé le mode debug profiling qu'en pense tu ? MERCI

[Eolia]

Ben sans modules, ça file^^

[kate66]

c'est a dire je ne comprends pas ? MERCI

[kate66]

il y a des scripts externe etrange qui on ete trouve.

Pense tu que cela peux provoquer l'erreur ?

 

 

[Mediacom87]

bonne chance @Eoliapour le support

 

[kate66]

Désole je ne comprends pas j'ai ouvert un autre sujet on m'indiquer plus le meme probleme est ce mauvais ? MERCI

[Mediacom87]

Dans votre cas je penche pour une cause identique pour les 2 soucis puisque cela touche à mon avis aux même requêtes.

Donc en corrigeant un vous corrigerez l'autre.

[Eolia]

il y a 8 minutes, Mediacom87 a dit :

bonne chance @Eoliapour le support

 

Non mais j'ai déjà répondu en MP que de toute façon je ne m'occupais pas de 1.7^^

[kate66]

ok j'ai indiqué à l'herbergeur pour la configuration mysql mais il n'ont rien trouvé d'incohrérent.

Si cela vient des requetes la aucune idée comment m'en sortir 😰

[Mediacom87]

Vous utilisez le pire hébergeur de la terre donc il ne faut rien attendre de lui.

[kate66]

j'ai 2 prestashop et aucun souci sur l 'autre mais sinon lequel est preferable ? MERCI

[kate66]

Si c'est un probleme de requete ca ne changera pas le pb de changer d'hebergeur il serait toujours la ? 

[kate66]

Voici la requete trop grosse je crois 

 public function getProducts(
        $idLang,
        $p,
        $n,
        $orderyBy = null,
        $orderWay = null,
        $getTotal = false,
        $active = true,
        $random = false,
        $randomNumberProducts = 1,
        $checkAccess = true,
        Context $context = null
    ) {
        if (!$context) {
            $context = Context::getContext();
        }

        if ($checkAccess && !$this->checkAccess($context->customer->id)) {
            return false;
        }

        $front = in_array($context->controller->controller_type, ['front', 'modulefront']);
        $idSupplier = (int) Tools::getValue('id_supplier');

        /* Return only the number of products */
        if ($getTotal) {
            $sql = 'SELECT COUNT(cp.`id_product`) AS total
                    FROM `' . _DB_PREFIX_ . 'product` p
                    ' . Shop::addSqlAssociation('product', 'p') . '
                    LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product`
                    WHERE cp.`id_category` = ' . (int) $this->id .
                ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') .
                ($active ? ' AND product_shop.`active` = 1' : '') .
                ($idSupplier ? ' AND p.id_supplier = ' . (int) $idSupplier : '');

            return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
        }

        if ($p < 1) {
            $p = 1;
        }

        /** Tools::strtolower is a fix for all modules which are now using lowercase values for 'orderBy' parameter */
        $orderyBy = Validate::isOrderBy($orderyBy) ? Tools::strtolower($orderyBy) : 'position';
        $orderWay = Validate::isOrderWay($orderWay) ? Tools::strtoupper($orderWay) : 'ASC';

        $orderByPrefix = false;
        if ($orderyBy == 'id_product' || $orderyBy == 'date_add' || $orderyBy == 'date_upd') {
            $orderByPrefix = 'p';
        } elseif ($orderyBy == 'name') {
            $orderByPrefix = 'pl';
        } elseif ($orderyBy == 'manufacturer' || $orderyBy == 'manufacturer_name') {
            $orderByPrefix = 'm';
            $orderyBy = 'name';
        } elseif ($orderyBy == 'position') {
            $orderByPrefix = 'cp';
        }

        if ($orderyBy == 'price') {
            $orderyBy = 'orderprice';
        }

        $nbDaysNewProduct = Configuration::get('PS_NB_DAYS_NEW_PRODUCT');
        if (!Validate::isUnsignedInt($nbDaysNewProduct)) {
            $nbDaysNewProduct = 20;
        }

        $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity' . (Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute,
                    product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '') . ', pl.`description`, pl.`description_short`, pl.`available_now`,
                    pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image,
                    il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
                    DATEDIFF(product_shop.`date_add`, DATE_SUB("' . date('Y-m-d') . ' 00:00:00",
                    INTERVAL ' . (int) $nbDaysNewProduct . ' DAY)) > 0 AS new, product_shop.price AS orderprice
                FROM `' . _DB_PREFIX_ . 'category_product` cp
                LEFT JOIN `' . _DB_PREFIX_ . 'product` p
                    ON p.`id_product` = cp.`id_product`
                ' . Shop::addSqlAssociation('product', 'p') .
                (Combination::isFeatureActive() ? ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop
                ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id . ')' : '') . '
                ' . Product::sqlStock('p', 0) . '
                LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl
                    ON (product_shop.`id_category_default` = cl.`id_category`
                    AND cl.`id_lang` = ' . (int) $idLang . Shop::addSqlRestrictionOnLang('cl') . ')
                LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl
                    ON (p.`id_product` = pl.`id_product`
                    AND pl.`id_lang` = ' . (int) $idLang . Shop::addSqlRestrictionOnLang('pl') . ')
                LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop
                    ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $context->shop->id . ')
                LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il
                    ON (image_shop.`id_image` = il.`id_image`
                    AND il.`id_lang` = ' . (int) $idLang . ')
                LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m
                    ON m.`id_manufacturer` = p.`id_manufacturer`
                WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . '
                    AND cp.`id_category` = ' . (int) $this->id
                    . ($active ? ' AND product_shop.`active` = 1' : '')
                    . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
                    . ($idSupplier ? ' AND p.id_supplier = ' . (int) $idSupplier : '');

        if ($random === true) {
            $sql .= ' ORDER BY RAND() LIMIT ' . (int) $randomNumberProducts;
        } else {
            $sql .= ' ORDER BY ' . (!empty($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . bqSQL($orderyBy) . '` ' . pSQL($orderWay) . '
            LIMIT ' . (((int) $p - 1) * (int) $n) . ',' . (int) $n;
        }

        $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql, true, false);

        if (!$result) {
            return [];
        }

        if ($orderyBy == 'orderprice') {
            Tools::orderbyPrice($result, $orderWay);
        }

        // Modify SQL result
        return Product::getProductsProperties($idLang, $result);
    }