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:
| Operators | Descriptions |
|---|---|
| + | A leading or trailing plus sign representing the search string must be present in each returned row. |
| – | A leading or trailing minus sign representing the search string must not be present in any returned rows. |
| > | It is used to change word’s contribution to the relevance value assigned to a row. The > operator increases the relevance value. |
| < | It is used to change word’s contribution to the relevance value assigned to a row. The > operator decreases the relevance value. |
| * | It indicates the wildcard at the end of the word. |
| ~ | It acts as a negation operator that negates the word”s ranking value. It is useful in marking noise words. |
| “” | It defines a phrase enclosed within double quote (“) characters. It matches the entire phrase for inclusion or exclusion instead of individual words. |
| () | The parenthesis operator is used to group words into subexpressions. It can also be nested and allowing them to be included, excluded, ranked, and so forth as a group. |
| @distance | It is only used in InnoDB tables that test whether two or more words start within a specified distance from each other or not. |
| No operator | If we have neither used plus or minus operator, by default, the word is optional, but the rows rated higher. |
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.