mysql全文布尔模式慢,MySQL全文布尔模式搜索返回太多结果

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)

This query however, returns rows containing Paul, without the 'Van' or 'Dyk'. Again, I want the query to return only rows that contain ALL of the keywords in EITHER the Artist or Track Name column.

Thanks in advance

解决方案

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.