MySQL MATCH AGAINST

Summary: in this tutorial, you will learn how to use the MySQL MATCH() and AGAINST() functions to perform full-text searches.

Introduction to MySQL MATCH() AGAINST() function

MySQL provides the MATCH() and AGAINST() functions to perform full-text searches:

• The MATCH() function accepts a comma-separated list of column names to be searched.
• The AGAINST() function takes a string to search for and an optional modifier that indicates the type of search.

The following illustrates the syntax for using the MATCH() and AGAINST() functions:

SELECT 
  * 
FROM 
  table_name 
WHERE 
  MATCH(column1, column2,...) 
   AGAINST('search_term' IN NATURAL LANGUAGE MODE);Code language: SQL (Structured Query Language) (sql)

In this syntax:

• table_name: The name of the table you want to search.
• column1, column2, ...: The columns in the specified table that you want to search within.
• 'search_term': The term or phrase you want to search for.
• IN NATURAL LANGUAGE MODE: This is the default search mode. Other modes include IN BOOLEAN MODE and WITH QUERY EXPANSION.

The following table compares the three modes when it comes to full-text search

MySQL MATCH AGAINST examples

We’ll create a posts table with a full-text index, insert some data into it and perform full-text searches to illustrate the MATCH() and AGAINST() functions.

First, create a table called posts:

CREATE TABLE posts(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    FULLTEXT(title, body)
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the posts table:

INSERT INTO posts (title, body) VALUES
('Introduction to MySQL', 'MySQL is a popular relational database management system.'),
('Advanced SQL Techniques', 'Learn advanced SQL techniques for optimizing queries.'),
('Web Development with PHP', 'Building dynamic websites using PHP and MySQL.'),
('Data Security Best Practices', 'Ensuring the security of your database and sensitive information.'),
('MySQL Performance Tuning', 'Optimizing the performance of your MySQL database.'),
('Database Design Principles', 'Designing efficient and normalized database structures.'),
('Full-Text Search in MySQL', 'Exploring the powerful full-text search capabilities of MySQL.'),
('Scaling MySQL for Large Datasets', 'Strategies for scaling MySQL to handle large datasets.'),
('Error Handling in MySQL', 'Best practices for handling errors in MySQL queries.'),
('Backup and Recovery Strategies', 'Implementing reliable backup and recovery strategies for MySQL.');Code language: SQL (Structured Query Language) (sql)

1) NATURAL LANGUAGE MODE

• Default Mode: If you don’t specify a mode, NATURAL LANGUAGE MODE is used by default.
• Natural Language Processing: In this mode, MySQL performs natural language processing on the search query. It tries to understand the context and relevance of the words in the search term.
• Relevance Sorting: The results are sorted by relevance, with the most relevant matches appearing first.
• Word Exclusion: Common words (stopwords) like “and,” “or,” and “the” are ignored unless you use double quotes to make them part of a phrase.

For example:

SELECT 
  * 
FROM 
  posts 
WHERE 
  MATCH(title, body) 
  AGAINST('database' IN NATURAL LANGUAGE MODE);Code language: SQL (Structured Query Language) (sql)

Output:

+----+------------------------------+-------------------------------------------------------------------+
| id | title                        | body                                                              |
+----+------------------------------+-------------------------------------------------------------------+
|  6 | Database Design Principles   | Designing efficient and normalized database structures.           |
|  1 | Introduction to MySQL        | MySQL is a popular relational database management system.         |
|  4 | Data Security Best Practices | Ensuring the security of your database and sensitive information. |
|  5 | MySQL Performance Tuning     | Optimizing the performance of your MySQL database.                |
+----+------------------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) BOOLEAN MODE

• Boolean Logic: This mode allows you to use boolean operators (AND, OR, NOT) and modifiers (+ for required, - for excluded, * for wildcard) in your search queries.
• Flexibility: Offers more flexibility in constructing complex search queries, allowing you to fine-tune the search logic.
• Word Significance: You can assign significance to words using + or - to influence the relevance of the results.

For example:

SELECT 
  * 
FROM 
  posts 
WHERE 
  MATCH(title, body) 
  AGAINST(
    'web AND full' IN BOOLEAN MODE
  );Code language: SQL (Structured Query Language) (sql)

Output:

+----+--------------------------------+-------------------------------------------------------------------+
| id | title                          | body                                                              |
+----+--------------------------------+-------------------------------------------------------------------+
|  7 | Full-Text Search in MySQL      | Exploring the powerful full-text search capabilities of MySQL.    |
|  3 | Web Development with PHP       | Building dynamic websites using PHP and MySQL.                    |
| 10 | Backup and Recovery Strategies | Implementing reliable backup and recovery strategies for MySQL.   |
|  4 | Data Security Best Practices   | Ensuring the security of your database and sensitive information. |
|  6 | Database Design Principles     | Designing efficient and normalized database structures.           |
+----+--------------------------------+-------------------------------------------------------------------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) WITH QUERY EXPANSION

• Query Expansion: This mode extends the search query by including the synonyms of the words in the original query. It can broaden the search results by including related terms.
• Relevance Sorting: Similar to NATURAL LANGUAGE MODE, the results are sorted by relevance.

For example:

SELECT 
  * 
FROM 
  posts 
WHERE 
  MATCH(title, body) 
  AGAINST('SQL' WITH QUERY EXPANSION);Code language: SQL (Structured Query Language) (sql)

Output:

+----+--------------------------+-------------------------------------------------------+
| id | title                    | body                                                  |
+----+--------------------------+-------------------------------------------------------+
|  2 | Advanced SQL Techniques  | Learn advanced SQL techniques for optimizing queries. |
|  5 | MySQL Performance Tuning | Optimizing the performance of your MySQL database.    |
|  9 | Error Handling in MySQL  | Best practices for handling errors in MySQL queries.  |
+----+--------------------------+-------------------------------------------------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Choosing the right search mode

Choosing the right mode depends on the nature of your search queries and the level of control you need over the search logic.

Here are the general guidelines:

• Use NATURAL LANGUAGE MODE for straightforward searches where natural language processing is sufficient.
• Use BOOLEAN MODE for more complex searches with boolean logic and fine-grained control over the search terms.
• Use WITH QUERY EXPANSION when you want to include synonyms and broaden the search scope.

In practice, you should experiment with different modes to find the most effective approach for your specific use case.

Summary

• Use MySQL MATCH() AGAINST() function to perform full-text searches.