1

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.

0

I state you have two possibilities with MySQL:

Deactivate stop word list

You can deactivate the stop word list completely.

To override the default stopword list, set the ft_stopword_file system variable. [...] The variable value should be the path name of the file containing the stopword list, or the empty string to disable stopword filtering.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-fine-tuning.html

(You might want to change ft_min_word_len too, since its default value is 4 and "go" is only 2 characters long.)

This would allow you to use the fulltext index even in case of presence of stop words, which offers by far more possibilities than any search with LIKE and is a lot faster.

Control the query

Your application can scan the search words before the query to actually change the query depending on "do the search words contain a stop word". If yes, query with LIKE, otherwise query with AGAINST. This way still leaves you with a very slow LIKE based query in the stop word case!

I do not know of further possibilities with MySQL as its capabilities concerning fulltext searches are rather limited.

Non-MySQL-Options (Take a look around)

There is other software around, which eases problems of search&find, for example the modified MySQL code Sphinx. Sphinx obviously has a different approach than MySQL since its default stop word list is empty. If there even is the possibility to add another software to your infrastructure, you might take a look at its possibilities or search for other software which might fulfill your needs (e.g. Apache Lucene).

  • "Control the query" is the best since if u remove all stop words then FullText search will be slow. Mysql said that. Besides, Like is much faster than FullText, based on my experiment it is about 3 times faster than FullText, the only downside of Like is it only search for '%searchTerm%' which will bring very small numbers of results – Tom Apr 17 '14 at 15:06
  • That is interesting. I would have expected the like query to be notably slower than the fulltext search. You can permute your search words around and build multiple or-connected like-clauses. WHERE xxx LIKE "%searchWord%" OR xxx LIKE "%Wordsearch%" and you also have the possibility to insert % signs in the middle of a search word. – GhostGambler Apr 17 '14 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.