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]

Hi I am not a developer. I am having issues with my website using prestashop platform. Currently, the site have about 7000 products & running on two servers. one is database server & one is webserver under Dedicated Virtual Server. both server have 24cores CPU & 32GB ram. The site is running on Ubuntu 12.04 LTS, use Apatch2-php5/fpm & use Varnish static catch. before that server use Apatch2-mod_php & thr site running faster, but after changing to php5_fpm, then the site start having many issues. Whenever 200 users visit the site, my website is loading really really slow. I got a report from our hosting provider saying that there are long queries in the database & consume alot of memory & cpu usage. The only way to bring the site back on is to restart the database server but this is only solving for temporary. They still can't find out the core issues whether it is the php issues or database issues. Can anyone give me an advise of how to fix this problem? I am really appreciate about it. Link to my website stelly.com.au My currently prestashop version is 1.4.8.2 Thanks Phong

Hi Prestashop supports, I have a major issues with my current site stelly.com.au. the site is loading really slow even I add more cpu & ram but it does not help at all. we are running ecommerce. We are currently carrying 6000 products on our store. My prestashop version is 1.4.8.2. We try to apply memcach but it does not work & therefore we move back to file system. We install varnish & store on memory. The site was running fine today till 5pm 17/10/13, then suddenly the site response really slow after that. We really need help in this case. Here is my server details web server: cpu: 24 cores ram: 32gb database server: cpu: 24 cores ram: 32gm I am looking forward to hearing from you soon. Kindly regards Phong