How to make the Front Office Search process faster

[patrmich]

Hello,

My shop is built with Prestashop 1.5.6.2

When I make a search (for instance word1 word2 word3), I can see that the here below sql query is made :
SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
                pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
             MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name ,(
                SELECT SUM(weight)
                FROM ps_search_word sw
                LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
                WHERE sw.id_lang = 2
                    AND sw.id_shop = 1
                    AND si.id_product = p.id_product
                    AND (sw.word LIKE 'word1%' OR sw.word LIKE 'word2%' OR sw.word LIKE 'word3')

Such query can takes 1 to 3 seconds.

I wonder whether it could be possible to reduce this time.

As far as I understood the above query is related to the following code in classes/Search.php :

$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, 
				pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
			 MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name '.$score.', MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute,
				DATEDIFF(
					p.`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 new
				FROM '._DB_PREFIX_.'product p
				'.Shop::addSqlAssociation('product', 'p').'
				INNER 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_.'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_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
				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 (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
				WHERE p.`id_product` '.$product_pool.'
				GROUP BY product_shop.id_product
				'.($order_by ? 'ORDER BY  '.$alias.$order_by : '').($order_way ? ' '.$order_way : '').'
				LIMIT '.(int)(($page_number - 1) * $page_size).','.(int)$page_size;

1- In such query, I cannot see the description field. However, when I search for a specific word only present in the description (I mean the long description) of one item, the search of this word returns the expected result.
What part of this sql query (or of another query) makes the search in the long description working ?

2- I have no need to search in the manufacturer product name.

3- I do not use Prestashop to manage products stock. So my goal is only to search in active products.

So, would you know what could be deleted in my search query to make the search process faster ?

I thank you in advance for any suggestion.

Patrick