Problems with modules
Posted by:
ingmar89
Jun 10, 2015 at 10:41 (9 year ago)
http://themeforest.net/item/supershop-multipurpose-responsive-prestashop-theme/10186257/comments?page=8&filter=all#comment_10241455
1) Group category module: bad optimization, so much JOIN in querys, 32-38 sec loading page wo cash
2) Vertical Mega Menus Module: same problem
3) Categories size module doesn’t work if product in multiple categories.
like this:
SELECT p.id_product, p.ean13, p.reference, p.id_category_default, p.on_sale, p.quantity, p.minimal_quantity, p.price, p.wholesale_price, p.quantity_discount, p.show_price, p.condition, p.date_add, p.date_upd,
product_shop.on_sale, product_shop.id_category_default, product_shop.minimal_quantity, product_shop.price, product_shop.wholesale_price, product_shop.show_price, product_shop.condition, product_shop.indexed, product_shop.date_add, product_shop.date_upd,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity,
pl.`available_later`, pl.`link_rewrite`, pl.`name`, MAX(image_shop.`id_image`) id_image,
il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),
INTERVAL 20
DAY)) > 0 AS new, product_shop.price AS orderprice FROM `product` p
LEFT JOIN `groupcategory_product_view` AS gv
On gv.productId = p.id_product
INNER JOIN product_shop product_shop
ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
LEFT JOIN `product_attribute` pa
ON (p.`id_product` = pa.`id_product`)
LEFT JOIN 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)
LEFT
JOIN stock_available stock
ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 AND stock.id_shop_group = 0 )
LEFT JOIN `category_lang` cl
ON (product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = 1 AND cl.id_shop = 1 )
LEFT JOIN `product_lang` pl
ON (p.`id_product` = pl.`id_product`
AND pl.`id_lang` = 1 AND pl.id_shop = 1 )
LEFT JOIN `image` i
ON (i.`id_product` = p.`id_product`) LEFT JOIN 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 `image_lang` il
ON (image_shop.`id_image` = il.`id_image`
AND il.`id_lang` = 1)
LEFT JOIN `manufacturer` m
ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE
product_shop.`id_shop` = 1
AND product_shop.`visibility` IN ("both", "catalog") AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `category_group` cg
LEFT JOIN `category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE cp.id_category IN (59, 60, 61, 62, 66, 102, 19) AND cg.`id_group` = 1
)
GROUP BY product_shop.id_product
ORDER BY gv.`total` DESC Limit 0, 12
1) Group category module: bad optimization, so much JOIN in querys, 32-38 sec loading page wo cash
2) Vertical Mega Menus Module: same problem
3) Categories size module doesn’t work if product in multiple categories.
like this:
SELECT p.id_product, p.ean13, p.reference, p.id_category_default, p.on_sale, p.quantity, p.minimal_quantity, p.price, p.wholesale_price, p.quantity_discount, p.show_price, p.condition, p.date_add, p.date_upd,
product_shop.on_sale, product_shop.id_category_default, product_shop.minimal_quantity, product_shop.price, product_shop.wholesale_price, product_shop.show_price, product_shop.condition, product_shop.indexed, product_shop.date_add, product_shop.date_upd,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity,
pl.`available_later`, pl.`link_rewrite`, pl.`name`, MAX(image_shop.`id_image`) id_image,
il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),
INTERVAL 20
DAY)) > 0 AS new, product_shop.price AS orderprice FROM `product` p
LEFT JOIN `groupcategory_product_view` AS gv
On gv.productId = p.id_product
INNER JOIN product_shop product_shop
ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
LEFT JOIN `product_attribute` pa
ON (p.`id_product` = pa.`id_product`)
LEFT JOIN 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)
LEFT
JOIN stock_available stock
ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 AND stock.id_shop_group = 0 )
LEFT JOIN `category_lang` cl
ON (product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = 1 AND cl.id_shop = 1 )
LEFT JOIN `product_lang` pl
ON (p.`id_product` = pl.`id_product`
AND pl.`id_lang` = 1 AND pl.id_shop = 1 )
LEFT JOIN `image` i
ON (i.`id_product` = p.`id_product`) LEFT JOIN 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 `image_lang` il
ON (image_shop.`id_image` = il.`id_image`
AND il.`id_lang` = 1)
LEFT JOIN `manufacturer` m
ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE
product_shop.`id_shop` = 1
AND product_shop.`visibility` IN ("both", "catalog") AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `category_group` cg
LEFT JOIN `category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE cp.id_category IN (59, 60, 61, 62, 66, 102, 19) AND cg.`id_group` = 1
)
GROUP BY product_shop.id_product
ORDER BY gv.`total` DESC Limit 0, 12
