MySQL Boolean Full-Text Searches

Summary: in this tutorial, you will learn how to perform MySQL Boolean full-text searches. In addition, you will learn how to use Boolean operators to form very complex search queries.

Introduction to MySQL Boolean full-text searches

Besides the natural language full-text search, MySQL supports an additional form of full-text search that is called Boolean full-text search. In the Boolean mode, MySQL searches for words instead of the concept like in the natural language search.

MySQL allows you to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators. This is why the full-text search in Boolean mode is suitable for experienced users.

To perform a full-text search in the Boolean model, you use the IN BOOLEAN MODE modifier in the AGAINST  expression. The following example shows you how to search for a product whose product name contains the Truck word.

SELECT productName, productline
FROM products
WHERE MATCH(productName) 
      AGAINST('Truck' IN BOOLEAN MODE )

Two products whose product names contain the Truck  words is returned.

To find the product whose product names contain the   Truck word but not any rows that contain  Pickup , you can use the exclude Boolean operator ( - ), which returns the result that excludes the Pickup the keyword as the following query:

SELECT productName, productline
FROM products
WHERE MATCH(productName) AGAINST('Truck -Pickup' IN BOOLEAN MODE )

MySQL Boolean full-text search operators

The following table illustrates the full-text search Boolean operators and their meanings:

Operator	Description
+	Include, the word must be present.
–	Exclude, the word must not be present.
>	Include, and increase ranking value.
<	Include, and decrease the ranking value.
()	Group words into subexpressions (allowing them to be included, excluded, ranked, and so forth as a group).
~	Negate a word’s ranking value.
*	Wildcard at the end of the word.
“”	Defines a phrase (as opposed to a list of individual words, the entire phrase is matched for inclusion or exclusion).

The following examples illustrate how to use boolean full-text operators in the search query:

To search for rows that contain at least one of the two words: MySQL or tutorial

‘mysql tutorial’

To search for rows that contain both words: mysql and tutorial

‘+mysql +tutorial’

To search for rows that contain the word “mysql”, but put the higher rank for the rows that contain “tutorial”:

‘+mysql tutorial’

To search for rows that contain the word “mysql” but not “tutorial”

‘+mysql -tutorial’

To search for rows that contain the word “mysql” and rank the row lower if it contains the word “tutorial”.

‘+mysql ~tutorial’

To search for rows that contain the words “mysql” and “tutorial”, or “mysql” and “training” in whatever order, but put the rows that contain “mysql tutorial” higher than “mysql training”.

‘+mysql +(>tutorial <training)’

To find rows that contain words starting with “my” such as “mysql”, “mydatabase”, etc., you use the following:

‘my*’

MySQL boolean full-text search main features

• MySQL does not automatically sort rows by relevance in descending order in Boolean full-text search.
• To perform Boolean queries, InnoDB tables require all columns of the MATCH expression has a FULLTEXT index. Notice that MyISAM tables do not require this, although the search is quite slow.
• MySQL does not support multiple Boolean operators on a search query on InnoDB tables e.g., ‘++mysql’. MySQL will return an error if you do so. However, MyISAM behaves differently. It ignores other operators and uses the operator that is closest to the search word, for example, ‘+-mysql’ will become ‘-mysql’.
• InnoDB's full-text search does not support the trailing plus (+) or minus (-) sign. It only supports the leading plus or minus sign. MySQL will report an error if you search word is ‘mysql+’ or ‘mysql-‘. In addition, the following leading plus or minus with wildcard are invalid: +*, +-
• The 50% threshold means if a word appears in more than 50% of the rows, MySQL will ignore it in the search result.

In this tutorial, you have learned how to perform MySQL Boolean full-text searches with many useful Boolean operators.