If you ever wanted to intergrate a better search engine into Magento, then SphinxSearch seams to be the easiest.
We have a windows based machine, and getting SphinxSearch to work on it is really easy, just go to the website, download the windows build and follow the instruction.
Get used to learning Sphinx before you use it with Magento, it took me a couple of days, but it is really flexable, very very quick and can use morphology i.e. searching for ‘Spades’ also retruns ‘spade’.
There are other settings as well so worth taking a look.
You need to create an index of the fulltext table, this means that it still uses your search options from magento.
index the table - we used the following settings
source search { type = mysql sql_host = localhost sql_user = root sql_pass = ******** sql_db = ********* sql_query_pre = SET NAMES utf8 sql_query = SELECT product_id, data_index FROM catalogsearch_fulltext }
and the index
index search {
source = search path = ***** (path to the shpinx index on your server)
obviously this is at its simplest settings.
You can index as many stores as you want by changing the sql_query ‘WHERE’ to the correct store.
The only Magent code that needs changing is Mage_CatalogSearch_Model_Mysql4_Fulltext
At the top you need to add:
include(' *****[path to your sphinx php api on your server] ');
then find the function
public function prepareResult($object, $queryText, $query)
and change the whole function to:
public function prepareResult($object, $queryText, $query) { if (!$query->getIsProcessed()) { $searchType = $object->getSearchType($query->getStoreId());
I’ve implemented sphinx in a similar way however there are way less search results than magento standard search.
Any ideas on tweaking magento+sphinx to understand something like “blue copacabana shirt size M” if the product information is:
title: copacabana shirt
size attribute: M (used in quick search, used in advanced searched set to yes)
color attribute: blue (used in quick search, used in advanced searched set to yes)
Hi, Sphinx should pick those details up, maybe try changing some Sphinx settings.
However couple of up updates to the script to add more and relevant results, mayby try adding the following will help (helped me)
$cl->SetLimits(0, 250);
Set the amount of results \\\"0 = first result, 250 last result, same as mysql LIMIT. currently defaults to 20 results in Magento.
Also setting the mysql insert for weight to string not decimal, as this doesn\\\’t calculate the relevance field in Magento. so:
Hi, Sphinx should pick those details up, maybe try changing some Sphinx settings.
However couple of up updates to the script to add more and relevant results, mayby try adding the following will help (helped me)
$cl->SetLimits(0, 250);
Set the amount of results \\\"0 = first result, 250 last result, same as mysql LIMIT. currently defaults to 20 results in Magento.
Also setting the mysql insert for weight to string not decimal, as this doesn\\\’t calculate the relevance field in Magento. so:
The results are returning the correct details by the look of it, plus the sql statement looks fine.
The sql is one long statement so that it doesn’t do to many inserts/updates.
If you look at the original sql statement you got
INSERT INTO catalogsearch_result (`query_id`, `product_id`, `relevance`) VALUES (1, 9115, 1.707) ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`)INSERT INTO catalogsearch_result (`query_id`, `product_id`, `relevance`) VALUES (1, 70489, 1.707) ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`)INSERT INTO catalogsearch_result (`query_id`, `product_id`, `relevance`) VALUES (1, 106933, 1.707) ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`)
its correct other than each insert needs a ; after it so it should read
INSERT INTO catalogsearch_result (`query_id`, `product_id`, `relevance`) VALUES (1, 9115, 1.707) ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`);INSERT INTO catalogsearch_result (`query_id`, `product_id`, `relevance`) VALUES (1, 70489, 1.707) ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`);INSERT INTO catalogsearch_result (`query_id`, `product_id`, `relevance`) VALUES (1, 106933, 1.707) ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`)
To make sure this happens just change
. " ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`)",
to
. " ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`);",
as i say the sql looks fine, have you tried running the sql manually to make sure their are no errors.
ie run
INSERT INTO catalogsearch_result (`query_id`, `product_id`, `relevance`) VALUES (1, 9115, 1.707) ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`);INSERT INTO catalogsearch_result (`query_id`, `product_id`, `relevance`) VALUES (1, 70489, 1.707) ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`);INSERT INTO catalogsearch_result (`query_id`, `product_id`, `relevance`) VALUES (1, 106933, 1.707) ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`)
through myphpadmin or what ever you use.
At least then it will show that the new search results have been inserted.
If it works the other thing you might need to change is
if (!$query->getIsProcessed()) {
if you remove the if statement (and end if) it will update the database each time you do a search, otherwise nothing will change until the search cache is refreshed.
I tried what you suggested, thank you. The INSERT statements are working ok (even without the ; between). Rows are being added to the catalogsearch_result table. Although every query_id = 1 (not sure if that is a problem)
I tried commenting out
if (!$query->getIsProcessed()) {
and also the endif
Still getting the message ‘Your search returns no results’. I’m baffled could it be that the query_id needs to be set? I noticed in the original function this line which appears to set the query_id field to match the auto increment catalogsearch_query.query_id field
$fields = array(
‘query_id’ => new Zend_Db_Expr($query->getId()),
‘product_id’,
);
Looking at my code, I’ve changed it since the original post, So I’ll post what i have now, which might help as i had the same problem as you, but now it works fine (remember to refresh your search cache.)
public function prepareResult($object, $queryText, $query)
{
$adapter = $this->_getWriteAdapter();
if (!$query->getIsProcessed()) {
$ids=false;
$sql= “INSERT INTO `{$this->getTable(’catalogsearch/result’)}` VALUES “;
$cl = new SphinxClient();
$cl->SetServer( “localhost”, 9312 );
//actual phrase matches
$cl->SetMatchMode(SPH_MATCH_PHRASE);
$cl->SetLimits(0, 400);
$q = str_replace(’+’,’|’,$queryText);
//search_piuphrase = your sphinx index
$cl->AddQuery ( $q, “search_piuphrase” );
$results = $cl->RunQueries();
foreach($results as $result){
if ( ! empty($result["matches"]) ) {
foreach ( $result["matches"] as $doc => $docinfo ) {
$ids[]=$doc;
}
}
}
bare in mind this is unedited from my script which uses three indexes for different types of search results, you’ll have to adapt it to your own index types.
Looking at my code, I’ve changed it since the original post, So I’ll post what i have now, which might help as i had the same problem as you, but now it works fine (remember to refresh your search cache.)
public function prepareResult($object, $queryText, $query)
{
$adapter = $this->_getWriteAdapter();
if (!$query->getIsProcessed()) {
$ids=false;
$sql= “INSERT INTO `{$this->getTable(’catalogsearch/result’)}` VALUES “;
$cl = new SphinxClient();
$cl->SetServer( “localhost”, 9312 );
//actual phrase matches
$cl->SetMatchMode(SPH_MATCH_PHRASE);
$cl->SetLimits(0, 400);
$q = str_replace(’+’,’|’,$queryText);
//search_piuphrase = your sphinx index
$cl->AddQuery ( $q, “search_piuphrase” );
$results = $cl->RunQueries();
foreach($results as $result){
if ( ! empty($result["matches"]) ) {
foreach ( $result["matches"] as $doc => $docinfo ) {
$ids[]=$doc;
}
}
}
bare in mind this is unedited from my script which uses three indexes for different types of search results, you’ll have to adapt it to your own index types.
