Prestashop slow query in blocklayered.php

[stellyaustralia]

Hi Prestashop community,

 

I am facing issues with this bad query provided from my hosting provider below.

SELECT c.id_category, c.id_parent, cl.name, (SELECT count(DISTINCT p.id_product) 

 

I attached part of the code in Blocklayered.php

This is the original code of this module. This code generated SQL and it become the most slowest query in my Prestashop.

I just wondering why there is a hash (#). Is it normal or a bug?
Also, This query is really very slow? Can anyone please recommend any solution to fix this query?

case 'category':
if (Group::isFeatureActive())
$this->user_groups = ($this->context->customer->isLogged() ? $this->context->customer->getGroups() : array(Configuration::get('PS_UNIDENTIFIED_GROUP')));

$depth = Configuration::get('PS_LAYERED_FILTER_CATEGORY_DEPTH');
if ($depth === false)
$depth = 1;

$sql_query['select'] = '
SELECT c.id_category, c.id_parent, cl.name, (SELECT count(DISTINCT p.id_product) # '
$sql_query['from'] = '
FROM '._DB_PREFIX_.'category_product cp
LEFT JOIN '._DB_PREFIX_.'product p ON (p.id_product = cp.id_product) ';
$sql_query['where'] = '
WHERE cp.id_category = c.id_category
AND '.$alias.'.active = 1 AND '.$alias.'.`visibility` IN ("both", "catalog")';
$sql_query['group'] = ') count_products
FROM '._DB_PREFIX_.'category c
LEFT JOIN '._DB_PREFIX_.'category_lang cl ON (cl.id_category = c.id_category AND cl.`id_shop` = '.(int)Context::getContext()->shop->id.' and cl.id_lang = '.(int)$id_lang.') ';

if (Group::isFeatureActive())
$sql_query['group'] .= 'RIGHT JOIN '._DB_PREFIX_.'category_group cg ON (cg.id_category = c.id_category AND cg.`id_group` IN ('.implode(', ', $this->user_groups).')) ';

$sql_query['group'] .= 'WHERE c.nleft > '.(int)$parent->nleft.'
AND c.nright < '.(int)$parent->nright.'
'.($depth ? 'AND c.level_depth <= '.($parent->level_depth+(int)$depth) : '').'
AND c.active = 1
GROUP BY c.id_category ORDER BY c.nleft, c.position';


$sql_query['from'] .= Shop::addSqlAssociation('product', 'p');

Generated query: 
SELECT c.id_category, c.id_parent, cl.name, (SELECT count(DISTINCT p.id_product) # 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) WHERE cp.id_category = c.id_category AND product_shop.active = 1 AND product_shop.`visibility` IN ("both", "catalog")) count_products FROM ps_category c LEFT JOIN ps_category_lang cl ON (cl.id_category = c.id_category AND cl.`id_shop` = 1 and cl.id_lang = 1) RIGHT JOIN ps_category_group cg ON (cg.id_category = c.id_category AND cg.`id_group` IN (1)) WHERE c.nleft > 3 AND c.nright < 62 AND c.level_depth <= 4 AND c.active = 1 GROUP BY c.id_category ORDER BY c.nleft, c.position

 

Thanks

Phong

 

W. http://stelly.com.au

E. [email protected]