mysql fulltext search not returning most relevant results first

Question

Right now I'm using the following mysql query

SELECT * FROM songs WHERE MATCH (title,artist) AGAINST ('$ser' IN BOOLEAN MODE)

Now it's working but not very well.

For example say I have the following terms in my database

Term One
Term Two
Term Three

and I do a search for Term Three

$ser = 'Term Three'

it's simply returning

Term One
Term Two
Term Three

So it's matching Term but it's not giving Term Three a higher priority having both Term and Three and I'm not sure what to do to get it to appear above the others.

Hope that makes sense.

Answer 1

With MyISAM tables, "three" is a stopword by default, therefore it is not indexed, and it is ignored in all searches.

If you are indeed using MyISAM tables, then you could specify your own custom list of stopwords, or disable the feature altogether, via the ft_stopword_file server option (MyISAM).

NB: For InnoDB tables, innodb_ft_server_stopword_table is the option to look for. "three" seems no to be a stopword by default for InnoDB, but the list of stopwords may be different on your server.

---

[edit]

I totally overlooked that boolean searches do not sort results automatically by relevance (as natural language searches do). All you need to do is add an ORDER BY clause (please test it here):

SELECT * FROM songs WHERE MATCH (title,artist) AGAINST ('$ser' IN BOOLEAN MODE)
ORDER BY MATCH (title,artist) AGAINST ('$ser' IN BOOLEAN MODE) DESC