MySQL - Full text search -

i'm using mysql db full-text index of 2 fields (name , aliases).

it videogames database

codice:

select name,match (name,aliases) against ('infamous: second son') relevance games_search order relevance desc; 

with query following results

codice:

infamous 2                     9.150630950927734 infamous: second son           9.150630950927734 infamous                       8.947185516357422 infamous: festival of blood    8.947185516357422 

why first 2 results have same relevance?

full text search in mysql ignores 2 types of words: stop words , words shorted threshhold.

you can read list of stop words here. of particular interest in case word 'second'.

by default, words 4 characters or longer kept search purposes. hence, 'son' ignored. so, query equivalent to:

select name, match (name,aliases) against ('infamous:') relevance games_search order relevance desc; 

i not sure why these higher last two. speculate relevancy treating perfect match (minus stop words , short words) higher imperfect match.