The most slow query found in Blocklayered module

[Bukhuu]

Hey masters of Prestashop.

 

I have 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 why very slow?

 

Does anyone know about this? Please help me

 

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

[selectshop.at]

The heavy and slow query problem for this module is a knowing bug. This module should be reworked completely. There are several entries on the debug tracker from my side there. But seems that there is no time for it, or they will do it step-by-step.

[Bukhuu]

The heavy and slow query problem for this module is a knowing bug. This module should be reworked completely. There are several entries on the debug tracker from my side there. But seems that there is no time for it, or they will do it step-by-step.

 

Yeah, Do you know anything about the hash(#) included SQL query?

[Bukhuu]

Is it possible to remove the hash(#) from this code?