[SOLVED] Sphinx Search returns less result as compared to MySql LIKE

Sphinx Search returns less result as compared to MySql LIKE

When I try simple MySql Query

select id 
from `contacts` 
where name LIKE '%abdul%'
OR email LIKE '%abdul%'
OR phone LIKE '%abdul%'

it returns 278 records from my database where as when I search through Sphinx search like this

$sphinx = new SphinxClient();
$sphinx->setServer('localhost',9312);
$sphinx->setLimits(0,1000);
$sphinx->setMatchMode(SPH_MATCH_EXTENDED2);
$result = $sphinx->query('abdul','test1');

It returns only 112 records. Is there any way to get same record as MySql Query in Sphinx?

Solution

Sphinx doesn't do wildcard search by default. Make sure to add

min_infix_len=2

to your index config, rebuild it and then try

$result = $sphinx->query('*abdul*','test1');

If it doesn't help you need to inspect thoroughly the documents that differ to understand the root cause, since there may be other reasons due to the fact that LIKE in MySQL just does basic wildcard search while Sphinx first tokenizes your text, builds in inverted index and the uses it for search. Much more complex process than just LIKE.