MySQL Natural Language Full-Text Searches

Summary: in this tutorial, you will learn about MySQL natural-language full-text search by using the MATCH()  and AGAINST() functions.

Introduction to MySQL natural-language full-text searches

In natural language full-text searches, MySQL looks for rows or documents that are relevant to the free-text natural human language query, for example, “How to use MySQL natural-language full-text searches”.

Relevance is a positive floating-point number. When the relevance is zero, it means that there is no similarity. MySQL computes the relevance based on various factors including the number of words in the document, the number of unique words in the document, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

To perform natural-language full-text searches, you use MATCH()  and  AGAINST() functions. The MATCH()  a function specifies the column where you want to search and the AGAINST()  function determines the search expression to be used.

MySQL natural-language full-text search example

We will use the products the table in the sample database for the demonstration.

First, create a full-text search in the productLine  column of the products  table using the ALTER TABLE ADD FULLTEXT  statement:

ALTER TABLE products 
ADD FULLTEXT(productline);

Second, you can search for products whose product lines contain the term Classic . You use the MATCH()  and AGAINST()  functions as the following query:

SELECT 
    productName, 
    productLine 
FROM products 
WHERE 
    MATCH(productLine) 
    AGAINST('Classic');

To search for a product whose product line contains Classic or Vintage the the term, you can use the following query:

SELECT 
	productName, 
    productLine 
FROM products 
WHERE 
	MATCH(productline) 
    AGAINST('Classic,Vintage')
ORDER BY productName;

The AGAINST()  function uses IN NATURAL LANGUAGE MODE  search modifier by default, therefore, you can omit it in the query. There are other search modifiers e.g.,  IN BOOLEAN MODE   for Boolean text searches.

You can explicitly use the IN NATURAL LANGUAGE MODE  search modifier in your query as follows:

SELECT 
	productName, 
    productLine 
FROM products 
WHERE 
	MATCH(productline) 
	AGAINST('Classic,Vintage' IN NATURAL LANGUAGE MODE)

By default, MySQL performs searches in a case-insensitive fashion. However, you can instruct MySQL to perform case-sensitive searches using binary collation for indexed columns.

Sort the result set by relevance

A very important feature of full-text search is how MySQL ranks the rows in the result set based on their relevance. When the MATCH()  the function is used in the WHERE clause, MySQL returns the rows that are more relevant first.

The following example shows you how MySQL sorts the result set by relevance.

First, create a full-text search for the  productName column of the products table.

ALTER TABLE products 
ADD FULLTEXT(productName);

Second, search for products whose names contain  Ford  and/or  1932:

SELECT 
	productName, 
    productLine 
FROM products 
WHERE 
	MATCH(productName) 
    AGAINST('1932,Ford');

Here is the output:

The products, whose names contain both 1932  and Ford are returned first and then the products whose names contain the only Ford keyword.

There are some important points you should remember when using the full-text search:

• The minimum length of the search term defined in the MySQL full-text search engine is 4. It means that if you search for the keyword whose length is less than 4 e.g., car, cat, you will not get any results.
• Stop words are ignored. MySQL defines a list of stop words in the MySQL source code distribution storage/myisam/ft_static.c

In this tutorial, you have shown how to use the MATCH() and AGAINST() functions to perform natural language searches in MySQL.