Help about fixing a slow query.

[tufik]

Hi all,

I have this exact same problem posted here :

 

https://www.prestashop.com/forums/topic/253715-fight-with-speed-now-sql-problem/

 

The query is exactly the same and the solution from the user kuskov in the topic above, was to remove the attributes part of the query.

 

I can remove that too and the website works very well and is extremely fast, but without that part, I don't have the possibility of showing the various attributes under a product. 

 

Is there a way to fix this query so it works correctly and fast, without having to remove the attributes ?

	$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`,
				pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image,
				il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
				DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),
				INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).'
					DAY)) > 0 AS new, product_shop.price AS orderprice
			FROM `'._DB_PREFIX_.'category_product` cp
			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->shop).'
			LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
				ON (product_shop.`id_category_default` = cl.`id_category`
				AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
			LEFT JOIN `'._DB_PREFIX_.'product_lang` pl
				ON (p.`id_product` = pl.`id_product`
				AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').')
			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)$id_lang.')
			LEFT JOIN `'._DB_PREFIX_.'manufacturer` m
				ON m.`id_manufacturer` = p.`id_manufacturer`
			WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
				AND p.`reference` LIKE "'.$prefix.'%" GROUP BY product_shop.id_product';

Thank you,

Tufik

[tufik]

Anyone any suggestion about this ?

[tufik]

Nothing? No one can help with this ?

[NemoPS]

Simply put: no.

You can split it into 2 though, grab products only first, then attributes. Also, that's the layered navigation one, right? The  MAX on the id_product_attribute is wrong, it doesn't pick the default on several ps versions I saw (mainly 1.5.6.2)

[tufik]

Simply put: no.

You can split it into 2 though, grab products only first, then attributes. Also, that's the layered navigation one, right? The  MAX on the id_product_attribute is wrong, it doesn't pick the default on several ps versions I saw (mainly 1.5.6.2)

 

Hi Nemo,

Thank you for your reply. Can I please ask you to show me what you exactly mean? How can I split it? Could you provide me a fixed version that I can test out? By the way, the Prestashop version is 1.6.0.8

 

Thank you in advance for this.

 

Regards

[tufik]

Hi Nemo,

Thank you for your reply. Can I please ask you to show me what you exactly mean? How can I split it? Could you provide me a fixed version that I can test out? By the way, the Prestashop version is 1.6.0.8

 

Thank you in advance for this.

 

Regards

Any suggestion about this Nemo? 

 

Thank you

[tuk66]

You have no index on the reference in ps_product table.

[tufik]

You have no index on the reference in ps_product table.

 

Hi tuk66,

Thank you for your reply. Can you please elaborate with an example? I noticed that the problem here, exactly as in the other case, in the other thread, was with the attributes.

Do you think instead that the problem relies in the missing indes for the reference column ?

Can you please elaborate with an example? 

 

Thank you in advance,

Tufik

[tuk66]

The reference field is used in your query. So the first thought is to add an index to ps_product table. Use phpMyAdmin or other tool for this.