Ok, here is the problem. I have a table
Text text nature .... text2 nature .... a lot of nature ... rows here.. .... go with nature with nature her go it
Now, i select:
select * From MyFullText where MATCH(text) AGAINST ('+go +with +nature' IN BOOLEAN MODE)
As a human being, we will bring out only 2 rows:
Text go with nature with nature her go it
However, MySql is not human & it shows all rows that have the word "nature" since "go" & "with" are stop words so it doesn't take into account these words.
What if we have 1000 rows containing the word "nature"? if that is the case then user will waste a lot of time for searching.
In this case
like '%go with nature%' does a much better job than fulltext.
So, can you find out an elegant solution that meets these requirements:
-Use "Fulltext ability for all non-stop words"
-If there are "stop words" in the search term then it should go back to use
LIKE as in
like '%go with nature%'
-Generally, it will be very smart to bring the best result depending on the search term.