Layered Navigation Block - SQL Timeout

[kuskov]

Hello guys,

 

i have came across a problem with the layered navigation block module in one category i get a timeout when loading the page. The problem i found, depends on the following query:

$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(product_attribute_shop.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)'.
			Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
			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));

Even mysql doesn't work and gives the ERROR 500. Does anyone know the solution?

 

Best regards,

[Dh42]

Raise the limit of your sql timeout on your server or upgrade to a server that processes requests faster. 

[kuskov]

I have a normal ecommerce hosting. This problem raises only in one specific category, where products have 15-20 combinations. The picking combinations filters is killing the db.

[kokosmin]

I have this problem, too. On a reseller account, pretty good server.

I have just TWO products for now, but each with 6-8 sizes and 24-27 colors so about 150-200 combinations per product.

Category doesn't load, gives timeout error.