some categories do not load!

[mhmdshv]

hi everyone

i've recently set up a shop using prestashop 1.6, and there is a problem which i can't imagine why occurs.

 

i have dozens of categories, each containing some products. when i try to access some specific categories, the browser will keep loading and finally i receive a timeout error ( the timeout is set to 60s or so). the i can reach its parent and sub category, but i can't load this one in frontend.

 

anyone else had this issue before?

 

any help is really appreciated.

 

[NemoPS]

Can you try disabling the layered navigation as a test? It might be killing the db, depending on how many products you have

[mhmdshv]

Tnx for good advice nemo. i disabled the module, and the pages are loading fine. but when i enable it, i get a timeout error.

 

this module is making problems for me, i have another post here : https://www.prestashop.com/forums/topic/424990-products-count-does-not-work-properly/

 

any idea how to repair it?

[NemoPS]

The fact is the module will die if it overloads the db over a certain amount. I tried splitting the getProductsByFilter sql into 2 some time ago. I can't access the file anymore, but I'm positive it was this one

			$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').'
			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').'
			'.Product::sqlStock('p', 'product_attribute_shop', 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)
			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));
		}

SPlit it up into 2, grab the ids only in the first call, then all other data using AND p.id_product IN (the list you got before)