Timeout because of Layered navigation module on Prestashop 1.6.0.6

[kokosmin]

Prestashop 1.6.0.6 on a good server - enough processing power and memory. My host verified that no script was overloading it.

 

I have just two test products, but each with 25-28 colors and 6-8 sizes, that would add up to 160 - 230 combinations per product.

When I click on category and the filter (Layered navigation) activates, the page does not load. After about a minute, the server throws Error 500 - request time out.

 

For finding out what is the cause, the first test was disabling the module with the two products being active, and the page is loading ok.

 

For the second test, I deactivated one of the products, and the category is accesible, and the filter is usable.

 

So I arrived to the conclusion that the module Layered Navigation cannot handle even 400 combinations from 2 products. Somewhere is a glitch. How do we find it?

 

Test website: http://www.fabricadetricouri.ro/2015/

Category with the problem http://fabricadetricouri.ro/2015/ro/16-tricouri

 

(I also deactivated URL rewriting for some tests, but no change).

Edited June 8, 2014 by kokosmin (see edit history)

[kokosmin]

Today the module had an update installed, but no change.

It still hangs the page untill server timeout.

 

Oh, and 100% not server problem because I copied the website to another hosting company for a trial, very good server, no change.

Edited June 12, 2014 by kokosmin (see edit history)

[NemoPS]

Yeah, I believe it's the module.  I came across something similar recently, with a 1.5.6.2 version. There were a lot more products (40) with many combinations. I ended up splitting the following into 2

			$this->products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
			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(pa.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)
			WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")
			AND '.(Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= '.(int)$parent->nleft.' AND c.nright <= '.(int)$parent->nright : 'c.id_category = '.(int)$id_parent).'
			AND c.active = 1
			AND p.id_product IN ('.implode(',', $product_id_list).')
			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));

I can't find my modification anymore, but I first got product ids only with all those conditions, then used the new array for an IN clause and was able to cut down loading times

[kokosmin]

Oh, it would be great if we could put this (bug?) under the attention of the developers.

 

I really dont't want to split my products into that many colors or 6 sizes, it's not natural user experience.

Edited June 13, 2014 by kokosmin (see edit history)

[kokosmin]

I have posted this on the forge  http://forge.prestashop.com/browse/PSCSX-2394?jql=labels%20%3D%20blocklayered

I hope I can get this problem solved. If anybody has this problem too, please vote on the forge or comment there.