Les meilleurs développeurs Prestashop freelances sont sur Codeur.com

Optimisation requête SQL Prestashop

 Terminé·30 à 300 €·1 offre·2720 vues


La requête suivante du module Blocklayered sur prestashop 1.5.6 prend plus de 10s sur mon serveur dédié. J'aimerais descendre en-dessous de 1seconde.

/modules/blocklayered/blocklayered.php
(autour de la ligne 2010)
SELECT
p.*,
'.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
'.$alias_where.'.id_category_default,
pl.*,
MAX(image_shop.`id_image`) id_image,
il.legend,
m.name manufacturer_name,
MAX(product_attribute_shop.id_product_attribute) id_product_attribute,
DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB(NOW(), INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
FROM `'._DB_PREFIX_.'category_product` cp
LEFT JOIN '._DB_PREFIX_.'category c ON (c.id_category = cp.id_category)
LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
'.Shop::addSqlAssociation('product', 'p').'
'.Product::sqlStock('p', null, false, Context::getContext()->shop).'
LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')
LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON (p.id_product = pa.id_product)'.
Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")
AND p.id_product IN ('.implode(',', $product_id_list).')
AND '.(Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= '.(int)$parent->nleft.' AND c.nright &lower;<= '.(int)$parent->nright : 'c.id_category = '.(int)$id_parent).'
AND c.active = 1
GROUP BY product_shop.id_product
ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).
' LIMIT '.(((int)$this->page - 1) * $n.','.$n));
########################################################
Ce code donne par exemple la requête suivante sur ma base de prod:
########################################################
# Query_time: [Téléphone visible pour les membres Pro] Lock_time: [Téléphone visible pour les membres Pro] Rows_sent: 12 Rows_examined: [Téléphone visible pour les membres Pro]
SELECT
p.*,
product_shop.*,
product_shop.id_category_default,
pl.*,
MAX(image_shop.`id_image`) id_image,
il.legend,
m.name manufacturer_name,
MAX(product_attribute_shop.id_product_attribute) id_product_attribute,
DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL 0 DAY)) > 0 AS new,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
FROM `ps_category_product` cp
LEFT JOIN ps_category c ON (c.id_category = cp.id_category)
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_stock_available stock
ON (stock.id_product = p.id_product AND stock.id_shop = 1 )
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product AND pl.id_shop = 1 AND pl.id_lang = 3)
LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop
ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)
LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 3)
LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT 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 AND product_attribute_shop.`default_on` = 1)
WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog")
AND p.id_product IN (10,11,12,13,14,15,17,18,20,21,24,25,26,27,28,29,30,31,33,34,35,36,37,38,39,40,45,46,47,48,50,51,52,53,54,55,57,59,61,62,63,65,66,67,68,70,74,76,77,79,81,89,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114)
AND c.nleft >= 3 AND c.nright <= 52
AND c.active = 1
GROUP BY product_shop.id_product
ORDER BY cp.position asc LIMIT 0,12;

Budget indicatif : 30 à 300 €

Publication : 05 juin 2014 à 19h48

Profils recherchés : Développeur Prestashop freelance, Développeur PHP freelance

Le profil du client est reservé aux prestataires abonnés

Créer un compte

Projet réalisé par Patrice P.

Patrice P.
Vaugneray, 69670

Chaque jour, des centaines de clients utilisent Codeur.com pour trouver un prestataire. Créez votre compte dès maintenant, remplissez votre profil et trouvez de nouveaux clients.

Trouver des nouveaux clients

Votre navigateur Web n’est plus à jour. Il ne permet pas d’afficher correctement le site Codeur.com.
Nous vous invitons à mettre à jour votre navigateur ou à utiliser un autre navigateur plus récent.