MySQL BOOLEAN FULLTEXT SEARCH

A Boolean search mode is an additional form of full-text search in MySQL. It is more word-driven than the natural language search that means it searches for words instead of the concept. It allows us to do a search based on very complex queries that include Boolean operators such as less than (<) or more than (>) operators, the plus (+) and minus (-) sign, subexpressions ("(" and ")"), double quotes (""), an operator that lowers the value's contribution to the results (~) and the wildcards.

Therefore this search mode is suitable for the experienced user because it offers a means to perform some very advanced searches. We can perform a full-text search in this mode by including the IN BOOLEAN MODE modifier in the AGAINST function.

Let us understand it with a basic example. Suppose we have a table named posts that contain the following data:

The following example displays the result about how to search for a post's title whose name contains the "Java" word:

Executing the query will return the two post name that contains the Java word:

Suppose we want to get the post whose post names contain the "MySQL" word but not any other rows that contain the "Java" word. In this case, we can use the exclude Boolean operator (-) to get the desired output. See the following query:

Executing the query will return the post name that contains the MySQL word, not the Java word:

Relevance Score

The MATCH() function assigned a relevance value for each row in the table and ordered them as highest relevance first. This value determines how it is relevant to the search term. The relevance score always comes in nonnegative floating-point numbers.

If we want to check text relevance and sorts the rows according to the highest relevance comes first, we can use the query as follows:

Executing the query, we will get the desired result:

Below is another example that lists the relevance values for each row, even the value is zero. We can get this result without using the MATCH() function in the WHERE clause. Here is the statement:

Executing the query, we will get the desired result:

MySQL Boolean FULLTEXT Search Operators

This table shows the operators used in the full-text Boolean search mode and their meanings:

Let us see various examples that illustrate the use of Boolean full-text operators in the search query:

1. If we want to search for rows that contain at least one of these words: Java or tutorial, we can use the below statement:

2. If we want to search for rows that contain both words: Java and tutorial, we can use the below statement:

3. If we want to search for rows that contain the word Java, but put the higher rank for the rows that contain MySQL:

4. If we want to search for rows that contain the word Java, but put the lower rank for the rows that contain MySQL:

5. If we want to find rows that contain words starting with "my", such as "MySQL", we use the below query:

MySQL Boolean FULLTEXT Search Characteristics

• MySQL Boolean full-text search does not automatically sort rows in order of decreasing relevance.
• If we want to perform Boolean queries in InnoDB tables, it requires a full-text index on all columns of the MATCH expression. However, it is not required in MyISAM tables, but here the search becomes slow.
• Full-text search on InnoDB tables does not support multiple Boolean operators on a single search word, e.g., '++database'. If we do this, MySQL will return the syntax error. However, it can be processed in MyISAM tables that ignore other operators and uses that operator, which is adjacent to the search word; for example, '+-database' will become '-database'.
• InnoDB full-text search only supports leading plus (+) or minus (-) sign, not trailing plus or minus sign. If we do this, MySQL will report a syntax error. For example, we can search '+database' but not 'database-' in InnoDB. Also, we cannot use the leading plus or minus with wildcard characters: +*, +-, etc.
• MySQL will ignore the 50% threshold (the word appears in more than 50% of the rows) in the search result.
• MySQL InnoDB full-text search does allow to use @ symbol in the Boolean full-text search.