I'm having an issue with my fulltext search query and I've pretty much gone through all the forums and threads I could find but I'm still having this issue.
I am using the MySQL Fulltext Boolean Mode search to return matching rows based on two columns (artist name and track title). The user can enter any phrase they wish into the search bar, and the results are supposed to be only rows that contain ALL parts of the search query in EITHER of the columns.
This is the query I have so far, and it works with most queries but for some it return results too loosely and I'm not sure why.
SELECT * FROM tracks WHERE MATCH(artist, title) AGAINST('+paul +van +dyk ' IN BOOLEAN MODE)
To enhance sorting of the results in boolean mode you can use the following:
SELECT column_names, MATCH (text) AGAINST ('word1 word2 word3') AS col1 FROM table1 WHERE MATCH (text) AGAINST ('+word1 +word2 +word3' in boolean mode) order by col1 desc;
Using the first
MATCH() we get the score in non-boolean search mode (more distinctive). The second
MATCH() ensures we really get back only the results we want (with all 3 words).
So your query will become:
SELECT *, MATCH (artist, title) AGAINST ('paul van dyk') AS score FROM tracks WHERE MATCH (artist, title) AGAINST ('+paul +van +dyk' in boolean mode) order by score desc;
Hopefully; you will get better results now.
If it works or do not work; please let me know.