Welcome to part 3 of this series on full-text indexing and searching in MySQL. In Part 1, we saw how MySQL provides full-text search capability via FULLTEXT indexing along with the three following distinct types of full-text searches:
- Natural Language Full-Text Searches
- Boolean Full-Text searches
- Query expansion searches
In Part 2, I described how to perform Natural Language full-text searches in Navicat for MySQL. Today's blog follows where part 2 left off and covers the next type of full-text searching: Boolean Full-Text searches.
Boolean Mode Described
Boolean mode is a more word-driven than the natural language search. As such, Boolean full-text search supports very complex queries that include Boolean operators. For experienced users, Boolean full-text searching offers a means to perform some very advanced searches.
Here's how it works:
To perform a full-text search in the Boolean mode, you include the IN BOOLEAN MODE modifier in the AGAINST expression. Recall that, in the last installment, we added a full-text index to the film table of the Sakila sample database so that we could perform full-text searches on the description field. Here's an example that returns all films whose descriptions contain the word "Butler":
Some More Complex Examples
The above search is simple enough to not require full-text searching. It gets a lot more interesting once you start doing things like excluding matches that contain certain keywords. For instance, we can find films whose descriptions contain the word "Butler" that are not documentaries by preceding the word "Documentary" with the exclude Boolean operator ( - ):
That returns 61 rows, compared to 73 for our previous query.
Multi-word Matching
We can also search for rows whose description match multiple words using the ( + ) include operator. Prefixing a word with it tells the search engine to only match rows that contain that word. That becomes an important distinction when there are multiple words, such as "+Butler Hunter Waitress". In that case, all rows whose description contains the word "Butler" and either of the other two words will be returned:
Contrast the above results with those produced by a query with both the words "Butler" and "Hunter" prefixed with the ( + ) include operator:
Now, matching rows must contain both "Butler" and "Hunter" but not necessarily "Waitress".
A Quick Word on Relevancy Rankings
Full-text searches rank results differently for InnoDB than MyISAM because InnoDB full-text search is modeled on the Sphinx full-text search engine, and the algorithms used are based on BM25 and TF-IDF ranking algorithms.
Some operators affect ranking so that we can further fine-tune results. For example, we can search for rows that contain the word "Butler" but rank the row lower if it contains the words "Hunter" or "Waitress":
Conclusion
In today's blog, we learned how to perform Boolean full-text searches using Navicat for MySQL. Interested in finidng out more about Navicat for MySQL? You can try it for 14 days completely free of charge for evaluation purposes!
For a full listing of the boolean full-text operators, take a look at the official MySQL docs.
