CODEWITHSUNDEEP
mysqlfull-text-search
jgr
jgr

Reputation: 2931

MySQL disable stopwords for one column

Is it possible to disable stopwords for just one table or even one column? I know there are options to disable it globally. I need this for using two types of searching:

a) Standard boolean mode search (here ignore all stopwords)
b) Autocomplete searching (here I don't want ignore any words because if user type 'the' I don't know if it's whole word or just first 3 letters. It's ok for me to have two columns with same data for example: title and title_autocomplete, but how can I disable stopwords mechanism for just one column?

Example for:

a) User type 'The' return nothing because 'the' is stopword
b) User type 'The' return all starting with 'the' or just containing 'the' word like 'Thermal..' or 'The most important..'

And I want have those two option using one instance of MySQL server.

Upvotes: 1

Views: 885

Answers (1)

Solarflare
Solarflare

Reputation: 11106

MySQL does not support different stoplists for different columns, nor can you e.g. temporarily dis- or enable the stoplist (without rebuilding the index) right before you do a search. But here are some ideas to work around that:

  • In case your standard search really should only look for complete words as your example implied, so e.g. a userinput of "the word" would result in a query like match(title) against ('+the +word' in boolean mode), while the corresponding autocomplete search would result in match(title) against ('+the* +word*' in boolean mode), you can simulate this behaviour directly in your application. Disable the stopwordlist in MySQL, but maintain your own internal stoplist in your application; check the user input and don't add these words to your search query (and don't execute anything when you don't have any words left).

  • You can add a MyISAM table with just your primary key and the title column and keep them in sync by triggers. InnoDB and MyISAM each have their own stoplists, so this might sound close to what you are looking for. But there are some major problems with this approach: MyISAM doesn't support transactions, so if you rollback changes in your InnoDB table, they won't rollback in MyISAM. If you are using MyISAM for your primary table (which you shouldn't), don't use transactions for that table or your data is static, this might be an option. Another problem is that MyISAM will be removed in the next version of MySQL.

  • Disable the stopwordlist, add a column title_withoutstopwords and maintain a "cleaned up" version of your title that you can use for your standard search. This means you need to write a function that removes all stopwords from a text. You can use the output of that function e.g. in a trigger to keep your columns in sync. This is probably the safest way to go, but will be a little bit lengthy.

  • Disable the stopwordlist and filter the results of your standard search afterwards in your client (or accept that the results might include the stopwords).

Upvotes: 2

Related Questions