mysql fulltext search not returning most relevant results first
full-text-search, mysql, search
Solution
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
Problem
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.