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

[derthis]

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.

Derthis.

[derthis]

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 May 21, 2012 by derthis (see edit history)

[derthis]

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

[derthis]

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?

[derthis]

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.

[thienxu]

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)).'%\'';

}

else

unset($words[$key]);

 

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).'%\'';

[feboici]

Hi, are you confident about your solution ? I'm quite afraid to do it... my version is 1.5.0.17, is wroking on this one ?

THanks for your help

[gypsy]

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)?

[letrof]

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 January 4, 2013 by letrof (see edit history)