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 documents relevant to the free-text natural human language query. MySQL represents the relevance as a positive floating-point number, where a relevance of zero indicates no similarity.

MySQL calculates relevance based on various factors, including the number of words and unique words in the document, the total number of words in the collection, and the number of documents (rows) containing a specific word.

To perform natural-language full-text searches, you use the MATCH() and AGAINST() functions.

MySQL natural language full-text search example

First, create a table called documents with a full-text index that includes the contents column:

DROP TABLE IF EXISTS documents;

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contents TEXT,
    FULLTEXT ( contents )
);

Second, insert some rows into the documents table:

INSERT INTO documents(contents)
VALUES
('MySQL Database'),
('MySQL'),
('Database'),
('SQL'),
('A fork of MySQL');Code language: JavaScript (javascript)

Third, search for the documents whose contents have the word “mysql”:

SELECT 
  id, 
  contents, 
  MATCH (contents) AGAINST ('mysql') relevancy 
FROM 
  documents 
WHERE 
  MATCH (contents) AGAINST ('mysql');Code language: JavaScript (javascript)

Output:

+----+-----------------+--------------------+
| id | contents        | relevancy          |
+----+-----------------+--------------------+
|  1 | MySQL Database  | 0.0492168664932251 |
|  2 | MySQL           | 0.0492168664932251 |
|  5 | A fork of MySQL | 0.0492168664932251 |
+----+-----------------+--------------------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)

Fourth, search for the documents whose contents have the words “mysql” and/or “database”:

SELECT 
  id, 
  contents, 
  MATCH (contents) AGAINST ('mysql,database') relevancy 
FROM 
  documents 
WHERE 
  MATCH (contents) AGAINST ('mysql,database');
Code language: JavaScript (javascript)

Output:

+----+-----------------+---------------------+
| id | contents        | relevancy           |
+----+-----------------+---------------------+
|  1 | MySQL Database  | 0.20757311582565308 |
|  3 | Database        | 0.15835624933242798 |
|  2 | MySQL           |  0.0492168664932251 |
|  5 | A fork of MySQL |  0.0492168664932251 |
+----+-----------------+---------------------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that the documents that have both words “MySQL” and “Database” have higher relevancy.

Fifth, search for the document with the exact phrase “MySQL Database” by placing the search term in double quotes:

SELECT 
  id, 
  contents, 
  MATCH (contents) AGAINST ('"MySQL Database"') relevancy 
FROM 
  documents 
WHERE 
  MATCH (contents) AGAINST ('"MySQL Database"');Code language: JavaScript (javascript)

Output:

+----+----------------+---------------------+
| id | contents       | relevancy           |
+----+----------------+---------------------+
|  1 | MySQL Database | 0.20757311582565308 |
+----+----------------+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Summary

Use MySQL natural language full-text search to enhance the accuracy of your search queries, providing more relevant results based on the context and meaning of the entered search terms.

Was this tutorial helpful?