patrmich Posted May 15, 2017 Share Posted May 15, 2017 Hello,My shop is built with Prestashop 1.5.6.2When 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now