MySQL Natural Language FULLTEXT Search

Natural Language FULLTEXT Search interprets the search string as a literal phrase in natural human language. It does not support special characters. If no modifier is specified or when the IN NATURAL LANGUAGE MODE modifier is specified, it is enabled by default.

In this search mode, MySQL performs searching for every row that is relevant to the natural human language query. We can find the relevance in a positive floating-point number. If it is zero, that means there is no similarity. Relevance is computed based on various factors, including the number of words, the number of unique words, the total number of words in the collection, and the number of rows that contain a particular word.

We can perform the natural language full-text search using the MATCH() and AGAINST() functions. The MATCH() function is used to specify the column name where we want to perform a search, and the AGAINST() function determines the search string to be used.

Example

Let us first create a table posts with a FULLTEXT index that includes the title and descriptions columns. Here is the query:

Next, we will fill records into this table to explain full-text search:

We will now perform a full-text search using the MATCH() and AGAINST() functions as following:

Here is the output where we have performed a full-text search against a string "tutorial":

If you want to get the posts information that contains Java and Workbench term, we can use the syntax as follows:

Executing the statement, we will get the below result:

We know that the AGAINST() function by default uses IN NATURAL LANGUAGE MODE search modifier; that's why we can omit it in the query. Let us use the IN NATURAL LANGUAGE MODE search modifier in the above statement and see the result:

Executing the query, we will get the same result as before:

If we want to count the number of matches, we can use the query as follows:

Here is the result:

If we want to retrieve the relevance values explicitly, we need to use the below syntax. This statement doesn't order the returned rows because it neither uses the WHERE nor ORDER BY clauses:

Executing the query, we will get the desired result:

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

Executing the query, we will get the desired result:

We must have to consider these points while using the full-text search in MySQL:

• MySQL full-text search engine defines the minimum length of the search term as 4. It means MySQL will not produce any result whose keyword length is less than 4, such as dog, jug, etc.
• It also ignored the Stop words. We can see the stop words list in the MySQL source code distribution storage/myisam/ft_static.c.