I have 5 fields across 3 tables in my database and I am trying to use SphinxSearch to make a search in those fields. I am trying to achieve this – for example I have three words to make a search word1, word2 and word3. And I want to get the results even if one word is in one field, second word is in another field etc. If I make a search like this:

SELECT * FROM pc_index WHERE MATCH('word1 word2 word3')

it works well and I get right results. But if I add word4 which is not presented in the previous result, it ruins everything and SphinxSearch returns nothing.

Another approach is to use OR operator, so query like this:

SELECT * FROM pc_index WHERE MATCH('word1 | word2 | word3 | word4')

This way I can find something even if there’s only one word presented. But I can’t find a way to make SphinxSearch sort the results by amount of the query words presented in the results. Another words – I want to get the results the way when first results contains the most amount of searching words, for example word1, word2 and word3, no matter how many times those words appear in the results.

I have tried different rankers, like SPH_RANK_WORDCOUNT, but they don’t make any difference. And I can’t use php to get the relevance since I use stemming and word from the query can be in the different form in the result.

Is there any way to achieve that performance?