Native Prestashop search module. Issues/Solved cases/Improvements/Other

[Dolke]

Hi to all,

 

I was accumulating many questions and concerns when it comes to native Prestashop search module and want to share them with the rest of the community. 

 

My thoughts are that some off these if not all are extremely important and need to be addressed immediately. Some are probably bugs, other on the other hand might be result of some other non Prestashop related stuff.

First some basic info:

• My store is running Prestashop 1.5.4.1
• It is running on a A2hosting SSD shared server located in their Iceland DC.
• My store URL is: www.dekom.co.rs (Serbia)
• Store, untill today, contains 3858 products.
• We are using instant search functionality. (we switched to regular search)
• We are using both back office and eMagicOne Manager for Prestashop to run our store and keep it up to date.

These are some of the issues with the native search module that my colleagues and I came across for the last couple of months working with the PS:

 

1. Missing products in the search index. Products not showing in the results on the front office.

 

 

This problem is quite serious. Reason why is more then obvious. If potential customers search for some spare part and don't find it, they wont buy it either.

For example, I had 2 products in my store containing this phrase: "šarka BEKO". When my colleague accidentally searched for the items he only got one result in return. He found this strange because he knew we have 2 similar products both containing this phrase so he reported to me.
 

I checked my BO and saw that Prestashop reports no missing products in the search index.

I went to store Manager application, it was reporting the same thing. No items needed to be indexed.

I tried deleting the cookies and browser cash.
I tired searching from 3 other PCs off which 2 were totally in a different physical location.

Even tried rebooting my PC 

Result was the same.

 

I switched back to Store Manager application and rebuild search index just for that product which was not showing up when search was performed, and surprise that fixed the issue.

 

So my question is: Who knows how many products are not shown in the search results but reported as indexed? And Is this Store Manager issue, Prestashop issue, bad index issue etc...?

 

2. Search module doesn't return product containing the string. word or a phrase that actual product name, description or some other product field contains.

 

 

This is also interesting problem. I'm no expert, and I don't know how easy or hard would be to make perfect search, returning good results by all criteria but here I noticed that sometimes I got what I was looking for and sometimes I wont get product in the result list only because it contains letter or one number more then my search phrase contains.

 

I will try to show this with the screenshots:

Screenshot01:

 

http://www.screencast.com/t/E8XRdNZQ0m

 

Screenshot02:

http://www.screencast.com/t/CncfTbvvdeX1

I hope these images will explain that the problem is.

 

3. Problem when I search for one item SKU and I got the right product but beside that I get one product which not only don't have that SKU, but don't have that number anywhere in all product fields.

 

 

I have already opened a topic for this issue. Here is the link:

http://www.prestashop.com/forums/topic/276047-strange-behavior-of-the-search-module/?do=findComment&comment=1408641

 

I will write about new issues if I come across any but these seem very serious and I really dont know how to deal with them. Hope you guys will help me.

 

Thank you all in advance.
Dean

Edited February 17, 2014 by Dolke (see edit history)

[itokas]

I see same problems and I need answers too...

[Dolke]

Hi,

 

This morning I was testing my search further and found out more issues. 

 

For example: Some of the parts that I'm selling are the parts for washing machines...
Cretan washing machine has this model no: WA 1500

Model number is sometimes written in these forms on many other online sources, catalogs, brochures and so on...

WA1500
WA-1500
WA 1500

For this washing machine I sell this spare part for example:

http://dekom.co.rs/tunel-gume-za-ves-masine-i-susilice/2037-tunel-guma-ves-masine-gorenje-412-442-543-583-607369-gorenje.html

If you navigate back to homepage and search for "WA1500" you will get it as a result because "WA1500" can be found in the description of the product.

 

You will also get another product below this one which doesn't have anything like "WA1500" and yet it will be returned as a result of that search. WHY?   But this is the same problem I stated in my first post.

Now lets go deeper into analysis >>> If you type "WA 1500" so now with the space between word and numbers, You wont be getting any results although phrase "WA1500" contains both "WA" and "1500". 

Then when I type "WA 15" It will show me the results, but not if I type "A1500"

I cant seem to find any logic in how does search module works and what it actually searches.
Can someone point some light towards this issue?


And can someone recommend me some alternative search module?

 

Thank you.
 

 

 

[Dolke]

Will this topic just pass unnoticed?

[Dolke]

Dave and I did some intensive research of some of the issues stated above. 

You can see our discussion here:
http://www.prestashop.com/forums/topic/276047-strange-behavior-of-the-search-module/page-2?do=findComment&comment=1419811

I really hope some one will address these issues asap.

Edited October 25, 2013 by Dolke (see edit history)

[PascalVG]

About problem 2:

In the second screenshot, there was searched for "2000 6561". The full text however (seen in screenshot 1) is

PROGRAMATOR T2000 6561...

 

The search index indexes on individual words and as it is now, wants you to give a search word from the beginning of the word, so

with "2000" the above product isn't found, but with "T20", it finds it.

 

To fix this, we need to edit the file Classes/Search.php

 

Search for LIKE (Ctrl-C)

and you will find some code like this: (Sample code from 1.5.6.0)

					FROM '._DB_PREFIX_.'search_word sw
					LEFT JOIN '._DB_PREFIX_.'search_index si ON sw.id_word = si.id_word
					WHERE sw.id_lang = '.(int)$id_lang.'
						AND sw.id_shop = '.$context->shop->id.'
						AND sw.word LIKE
					'.($word[0] == '-'
						? ' \''.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''
						: '\''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''
					);

				if ($word[0] != '-')
					$score_array[] = 'sw.word LIKE \''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';
			}
			else
				unset($words[$key]);

Here, you can see that it doesn't allow for any other characters BEFORE the searched word, only after (the '%' character indicates 0 or more of 'any character')

 

so, to fix this, change the code a little like this: (find the lines in the code above and add the % three times:

...

? ' \'%'.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

: '\'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

);

 

if ($word[0] != '-')

 $score_array[] = 'sw.word LIKE \'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';

...

 

 

That should do the trick.

pascal

[PascalVG]

BTW, not too long ago, someone had some similar questions, and had some specific question about searching TAGS. Maybe you're interested in implementing that as well (He wanted TAGS be exactly the same as searched tag (by clicking on a tag in the tags block module), so a tag of 'iPo' should not be the same as a Tag 'iPod' which should not be the same as a Tag 'iPod Shuffle'

 

see here for fix:

http://www.prestashop.com/forums/topic/278859-solved-search-for-tags-vs-search-via-search-input-box/

 

For your info,

pascal

[PascalVG]

Dolke,

a word of caution: I don't have a big database available to test on, but please check if the addition of the %'s (to search for word-parts inside words, not only 'beginning with') doesn't slow down the search process significantly... I'm not sure how optimized LIKE-search is in mySql.

 

Please let me know the results of this,

thanks!

pascal.

[stottycabanas]

Hi Dean,

 

Here are my observations/findings/etc. ....

 

Post #1, item 1. You said: "I went to store Manager application, it was reporting the same thing. No items needed to be indexed." and then: "I switched back to Store Manager application and rebuild search index just for that product". I assume that's a feature of Store Manager that isn't in PS. Those 2 statements indicate an issue with Store Manager at the very least, and maybe PS as well. If you have a backup of the database prior to making the change in Store Manager that fixed the issue, you could restore that & try rebuilding the index in PS instead Then see what both Store Manager & PS report, and whether the issue is fixed or not.

 

Post #1, item 2. Pascal has covered the main points, but I'll elaborate a little based on further investigation I've been carrying out. PS builds up its table of valid search words by trawling through various tables collecting information such as product descriptions, category names, tags, supplier names, etc. It will strip out any symbols and punctuation marks, and validate against the minimum search word length. A single word will have a unique key for each shop/language combination in which it is found. PS also builds a table which links each of those unique search word records to all products with associated tables containing that word.

 

When you enter a search term or terms, PS will remove symbols & punctuation marks; leave out any words that are below the minimum length; check each remaining term against the table of valid search words; if all search terms can match against the beginning part of a valid search word, PS will return any products that have links to all the matched search words (and that match the current shop/language).

 

It is important to note that, as Pascal said, PS builds the search around individual words. However, care is needed to understand how that works. If you have a product called for example 'Horn-rimmed eye-shades', PS will ditch the hyphens and create search words 'hornrimmed' and 'eyeshades' in the database. If you search for 'rimmed' or 'shades', this product will not be returned. However, if you search for 'ho&rn-ri' or 'e....yes%h', you will get a match to that product. May not be intuitive, but that's the way it works!

 

I think the above should also address the queries you were raising in post #3 about the WA1500 variations (NB your minimum search word length is 2, which is why 'WA' and '15' are accepted as search terms.

 

Post #1, item 3. This relates to the query in the other thread you raised, and I think we're waiting to see whether the issue remains if you empty the search tables and rebuild them from scratch (in PS).

 

Think that's about it. Cheers for now, Dave

[Dolke]

Hi Pascal hi Dave,

 

I want to thank you both for these detailed answers and suggestions. I really appreciate it.

Sorry for a delay, I was absent from the forums for over a week.

I will try some of the things you wrote above and get back to you. Once again sorry for a delay. 

Cheers
Dean

[faDdy]

Hi PascalVG,

 

so, to fix this, change the code a little like this: (find the lines in the code above and add the % three times:

...

? ' \'%'.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

: '\'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

);

 

if ($word[0] != '-')

 $score_array[] = 'sw.word LIKE \'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';

 

 

I have tested your solution on a very big database around more than 150000 products on Prestashop 1.4.8.2, your solution worked perfectly but it has really slowed down the search performance. Specially when you search something by Brand name it takes around 3 minutes to load search results.

 

I am running my store on VPS server with 4GB ram and memory limit etc everything is configured properly.

 

 

Any thing you can help me to fix it ?

 

 

Thank you.

[PascalVG]

Hi faDdy,

I'm afraid the current setup of indexation and searching through it for large databases is just not adequate enough. Especially when adding my search inside text, instead of only from beginning doesn't help as it has to go through every indexed word separately, which does slows down the process significantly, as you noticed. The use of LIKE is just not fast enough.

 

It would take a whole rewrite of the way how to search, and how to index, to make it faster, i'm afraid.

Not sure if there is an add-on module that does a quicker search.

 

My 2 cents,

pascal.

[Christin_a]

About problem 2:

In the second screenshot, there was searched for "2000 6561". The full text however (seen in screenshot 1) is

PROGRAMATOR T2000 6561...

 

The search index indexes on individual words and as it is now, wants you to give a search word from the beginning of the word, so

with "2000" the above product isn't found, but with "T20", it finds it.

 

To fix this, we need to edit the file Classes/Search.php

 

Search for LIKE (Ctrl-C)

and you will find some code like this: (Sample code from 1.5.6.0)

					FROM '._DB_PREFIX_.'search_word sw
					LEFT JOIN '._DB_PREFIX_.'search_index si ON sw.id_word = si.id_word
					WHERE sw.id_lang = '.(int)$id_lang.'
						AND sw.id_shop = '.$context->shop->id.'
						AND sw.word LIKE
					'.($word[0] == '-'
						? ' \''.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''
						: '\''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''
					);

				if ($word[0] != '-')
					$score_array[] = 'sw.word LIKE \''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';
			}
			else
				unset($words[$key]);

Here, you can see that it doesn't allow for any other characters BEFORE the searched word, only after (the '%' character indicates 0 or more of 'any character')

 

so, to fix this, change the code a little like this: (find the lines in the code above and add the % three times:

...

? ' \'%'.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

: '\'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

);

 

if ($word[0] != '-')

 $score_array[] = 'sw.word LIKE \'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';

...

 

 

That should do the trick.

pascal

Hi, guys.

I tested it. This code corrects bugs, but else creates a new one.

I work with Prestashop 1.5.6 and if I use this code, it breaks indexing. If I try to recreate the index, Presta can`t do it. I think, the reason is in this code, cause if I delete it, searching becomes correct.

 

Can you help me? I promise to learn english))

 

Best regards from russian people!

[faDdy]

Don't know about prestashop 1.5 but on 1.4.8.2 it works fine, the only issue is if you have alot of products then it effects search performance and thanks PascalVG for your help.

[Christin_a]

It`s really has bugs. If I create a new good, i can`t find it. But with old goods all is correct.

[PascalVG]

Hi Christin_a,

When I have time, I will have a look at it. Little busy now, though,

pascal

[Christin_a]

Hi Christin_a,

When I have time, I will have a look at it. Little busy now, though,

pascal

It`s great! Thnx!

[Dolke]

It was more then 2 months ago I stared this topic and postponed further investigation for too long unfortunately. 

Thank you for your comments guys. I will implement and test your suggestions and get back to you asap.

I do need help in order to do one test that I recently started when it comes to search functionality.

I need fully working Prestashop website that runs on 1.5.6.X (preferably latest stable version) loaded with product that I can use to test search. Do you happen to know few of those?

 

Thank you.
Dean

[RayBez]

Dolke,

a word of caution: I don't have a big database available to test on, but please check if the addition of the %'s (to search for word-parts inside words, not only 'beginning with') doesn't slow down the search process significantly... I'm not sure how optimized LIKE-search is in mySql.

 

Please let me know the results of this,

thanks!

pascal.

 

I agree on the caution.  On large databases this modification could paralyse your searches.  You should ideally look at making use of Reverse Indexes.  I am new to Prestashop and haven't looked into it before on MySql, but this is a common issue and also occurs in M$Sql so I have had to deal with it for custom developed sites.  It is therefore a limitation of the DB and not of Prestashop itself as far as I can tell.  Reading this post I will definitely be doing further research on how to implement it in Prestashop as search is critical for any eCommerce site.

 

Regards,

Ray

[PascalVG]

Hi Ray, thanks for the confirmation that this "quick-fix" only works for smaller databases. I was afraid so.

 

Look forward to solutions to this! As you said, this is a common requirement in database apps, so there must be some smart guys/girls who found a solution to make a speedy free text search in large sized ones!

 

Thanks again,

Pascal

[faDdy]

Hi,

 

I am using Prestashop 1.4.8.2 with around 270000 products and this solution is working fine for me , the only thing i changed from PascalVG solution is that , i am using % only on these two lines :

 

? ' \'%'.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

: '\'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

);

 

If you Add % on this line :

$score_array[] = 'sw.word LIKE \'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';

 

it will slow down the search process.

 

Thank you PascalVG.

[PascalVG]

After some little research, there seems to be an option FULLTEXT indexing, in MySQL (since MySQL 5.6.4 also in InnoDB tables)

Which may be a good option to speed up the search:

http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html#innodb-fulltext-index

 

Some additional options are here:

 

http://stackoverflow.com/questions/1381186/fulltext-search-with-innodb

 

My 2 cents,

pascal.

[rh1n0]

 

Hi,

 

I am using Prestashop 1.4.8.2 with around 270000 products and this solution is working fine for me , the only thing i changed from PascalVG solution is that , i am using % only on these two lines :

 

? ' \'%'.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

: '\'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

);

 

If you Add % on this line :

$score_array[] = 'sw.word LIKE \'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';

 

it will slow down the search process.

 

Thank you PascalVG.

 

I've implemented this on my site and it does exactly what I wanted. I only have ~2,000-3,000 products enabled at any given time, so I doubt I will see any "slow" search performance if you have 270,000 and think it's fine.

 

Thanks all! Should really be the way this works by default. Wonder if newer versions have fixed this. I'm on 1.5.4.0 with too many customisations to ever upgrade.

[chris0241]

Hi all,

 

Is there a way to force Presta to reindex searches?

 

I have items not being found...

 

Thanks,

 

Chris

http://www.PROtastic.co.uk

[PascalVG]

hi Chris,

see preferences->search. 2nd block = reindex

 

 

pascal

[chris0241]

Thanks Pascal, SOLVED

[doron]

Hi All

 

How do I get the search to see spaces or dashes that we use in our reference codes from suppliers

E.g. Need the search to find AX-1012635/2 and AX 1012635/2 not just AX_1012635/2

 

We are using PS 1.606

 

Regards

Doron

[trevorgilligan]

thanks folks for all replies, esp @pascalvg i wonder can people help. the new version of ps1.6 has "search within word" which you can search for "in" and "rain" will be returned. i dont want to upgrade ps but im wondering where i would change the code so that i can set "search within word" == true and i can get this? thank you trev

[PascalVG]

Hi trevor,

The setting toggles some pre/post strings that are added to the search string like this: (see red code) (Sample code of PS. 1.6.0.12)

 

in file: classes/Search.php, function:

 

public static function find($id_lang, $expr, $page_number = 1, $page_size = 1, $order_by = 'position',

$order_way = 'desc', $ajax = false, $use_cookie = true, Context $context = null)

{

  if (!$context)

    $context = Context::getContext();

  $db = Db::getInstance(_PS_USE_SQL_SLAVE_);

 

  // TODO : smart page management

  if ($page_number < 1) $page_number = 1;

  if ($page_size < 1) $page_size = 1;

 

  if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way))

    return false;

 

  $intersect_array = array();

  $score_array = array();

  $words = explode(' ', Search::sanitize($expr, $id_lang, false, $context->language->iso_code));

 

  foreach ($words as $key => $word)

    if (!empty($word) && strlen($word) >= (int)Configuration::get('PS_SEARCH_MINWORDLEN'))

    {

      $word = str_replace('%', '\\%', $word);

      $word = str_replace('_', '\\_', $word);

      $start_search = Configuration::get('PS_SEARCH_START') ? '%': '';

      $end_search = Configuration::get('PS_SEARCH_END') ? '': '%';

 

      $intersect_array[] = 'SELECT si.id_product

          FROM '._DB_PREFIX_.'search_word sw

              LEFT JOIN '._DB_PREFIX_.'search_index si ON sw.id_word = si.id_word

          WHERE sw.id_lang = '.(int)$id_lang.'

              AND sw.id_shop = '.$context->shop->id.'

              AND sw.word LIKE

              '.($word[0] == '-'

? ' \''.$start_search.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).$end_search.'\''

: ' \''.$start_search.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).$end_search.'\''

);

 

      if ($word[0] != '-')

        $score_array[] = 'sw.word LIKE \''.$start_search.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).$end_search.'\'';

    }

    else

      unset($words[$key]);

...

 

 

(N.B. It may be that the two blue lines have been edited as well, to make the search from start dependent on the $start_search and $end_search variables, instead of hard coded 'always start at the beginning'..)

 

Hope this helps,

pascal.

[trevorgilligan]

@pascalvg thanks so much i will try all that you said and get it to work it means a lot that you replied and so fast , have a nice weekend all

[SDom]

I Everyone, we have migrated our prestashop from simple to multi-shop, everything works great except some product research.

The research work for many product but not for some ... I try the descprited solutions in this post but nothing works ...

Need help

 

Thx by advance

 

EDIT: I have the same problem as Dolke in first post, part 2

Edited October 6, 2016 by SDom (see edit history)

[Nicolai_P]

I have the same problem on Prestashop 1.6.1.11

 

I have a product named "Myshop   #63 - Some title "

 

If i seach for 63 it wont return any result, but i type "some" the product is shown.

 

I have also tried erasing the # and still it doesn't work.

 

Anyone has a fix that works with PS 1.6 ?

 

Update: Fixed it disabling instant search

Edited June 18, 2017 by Nicolai_P (see edit history)