Jump to content

Search Doesn't Work on Live Server - Index is Too Big?


Recommended Posts

Hi Everyone,


I am experiencing dificulties with search. Everything worked fine untill very recently, our e-shop is approaching deployment, and we have about 500 items now (which are very content heavy - electronics, a lot of texts and features etc.). Suddenly, search stopped working.


I am using YaSearch as my advanced search module, but simple queries goes to search.php?search_query=, which, I believe, is the default PrestaShop search engine.


However, these queries are unanswered. (No results were found)


I tried to move the whole DB to localhost for investigation, I found out that the problem is withing the search index. On localhost, I managed to re-build it, and it started working - however, re-building the index on live server fails (probably due to timeout) and copying tables ps_search_index and ps_search_word from local to live server does not work either.


I don't know how to solve this problem. Is there some module which enhances the search performance? My ps_search_index is 130.000 (approximately) long and even reductions (setting some weights to zero) made it only 10x smaller (= 13.000 entries). I guess live server has some limitations as of the number of DB operations per second.


Has anyone experienced the same problem? How do you make your front-end search work? I can't imagine the size of search index when having not only 500 items, but e.g. 1000 or 5000 items.


Thanks for any advice.


Link to comment
Share on other sites

In BO, Preferences -> Search, it says "Indexed items: 516 / 516". However, tables in DB search_index and search_word are both empty. Is there any way to reset it manually? I am wondering.


I found out that if I transfer index from localhost, search doesn't work (as I said before), but queries on DB in format:


SELECT 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.word LIKE 'something'

(copied from classes/Search.php)


work! And they take almost no time (0.0005 s). So could be 130.000 long index problematic? Or problem could be somewhere in PrestaShop setup?

Edited by derthis (see edit history)
Link to comment
Share on other sites

I added an array inside classes/Search.php, which I then return as a debug parametr and which I print in smarty {$debug|@print_r}. In this debug array I collect SELECT statements, counts, etc. On localhost, it works fine and it prints out the data.. However, on Live Server, the same code doesn't work and this array is not even empty, but seems not initialized at all. I fear there is some problem with calling Search.php, or its functions respectively. Every other aspect of PrestaShop however works on this exact same server.


Server info:

PHP: 5.3

MySQL: 5.5

Link to comment
Share on other sites

OK, I found the problem:


It's Search::sanitize function.


Doesn't work on Live Server, works on Localhost. It breaks Search::find and Search::indexation.

Don't know what could cause this. Any ideas?

Link to comment
Share on other sites

It is this line in classes/Search.php in sanitize function


$string = preg_replace('/['.PREG_CLASS_SEARCH_EXCLUDE.']+/u', ' ', $string);


commenting it out solves the problem. However, I fear it could damage search functionality in other way. If you know why this line is problematic on some servers and how to fix it, I would appreciate it.

Link to comment
Share on other sites

  • 4 weeks later...

My solution is replacing code of FO search by BO search.


FO search uses search_word and search_index tables but BO search uses SQL query directly.


Find these code in /classes/search.php:



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

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


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

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


$intersectArray[] = 'SELECT 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.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] != '-')

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





replace by:



$intersectArray[] = 'SELECT distinct p.id_product

FROM ps_product p

LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = '.(int)$id_lang.')

WHERE pl.name LIKE \'%'.pSQL($expr).'%\'';

  • Like 1
Link to comment
Share on other sites

  • 4 months later...
  • 1 month later...

hey theinxu great fix!! thank you so much ive spend countless hours trying to fix this problem and boom you got it! Funny part is my problem was not that it was too big my problem was it just randomly stopped working one day. I have only about 150 items. I treid reloading my product index changing bunch of things no luck i said what the hell lets try your method on my problem and it worked thank you!


DO YOU OR ANYONE SEE THIS CAUSING A PROBLEM (changing the search.php)?

Link to comment
Share on other sites

  • 5 weeks later...

the problem i see is that after replacing the code, it searches only by the name, not description. Could someone please tell me is it possible to make this search by both: title and description?


However it's still better than no search results at all:)


Thanks in advance

Edited by letrof (see edit history)
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...