MySQL - Natural Language Fulltext Search

MySQL: Natural Language Full-Text Search

Standard SQL LIKE clauses are often insufficient for performing meaningful searches on large blocks of text. MySQL’s full-text search capability provides a sophisticated way to search for keywords within text-based columns. The IN NATURAL LANGUAGE MODE is the default and most intuitive search mode, ranking results by relevance.

MySQL’s full-text search engine offers three main modes:

Natural Language Mode: Interprets the search string as a phrase in natural human language, returning results based on relevance.
Boolean Mode: Allows for complex searches using operators like + (must include), - (must not include), and * (wildcard).
Query Expansion Mode: A two-stage search that broadens the search to include words commonly associated with the initial search terms.

This tutorial focuses on the IN NATURAL LANGUAGE MODE.

Setting Up for Full-Text Search

To use full-text search, you must first create a FULLTEXT index on the text-based columns you wish to search (e.g., CHAR, VARCHAR, TEXT). This special index allows MySQL to efficiently parse and locate keywords within the text.

Syntax

The basic syntax for a natural language full-text search uses the MATCH() and AGAINST() functions:

SELECT column1, column2, ...
FROM table_name
WHERE MATCH(column_to_search1, column_to_search2)
AGAINST('your search phrase' IN NATURAL LANGUAGE MODE);

The IN NATURAL LANGUAGE MODE part is optional as it’s the default behavior, but explicitly stating it improves clarity.

Example

Let’s create a table of articles and populate it with some data. We will create a FULLTEXT index on the title and content columns.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    FULLTEXT KEY `ft_articles` (title, content)
) ENGINE=InnoDB;

Now, insert some records:

INSERT INTO articles(title, content) VALUES
('Introduction to SQL', 'SQL is the standard language for relational database management systems.'),
('Advanced MySQL Techniques', 'This guide covers advanced topics in MySQL, including indexing and database optimization.'),
('Learning a New Programming Language', 'Java and Python are popular programming languages for backend development.'),
('The Importance of Database Backups', 'Regular database backups are crucial for disaster recovery.');

Let’s search for articles about ‘database management’.

SELECT id, title, content
FROM articles
WHERE MATCH(title, content)
AGAINST('database management' IN NATURAL LANGUAGE MODE);

Output and Relevance Ranking

MySQL calculates a relevance score for each row. Rows with higher relevance are listed first. The score is based on factors like the number of keywords found and their rarity across the indexed documents.

id	title	content
1	Introduction to SQL	SQL is the standard language for relational database management systems.
4	The Importance of Database Backups	Regular database backups are crucial for disaster recovery.
2	Advanced MySQL Techniques	This guide covers advanced topics in MySQL, including indexing and database optimization.

Notice that the first result contains both ‘database’ and ‘management’ (implicitly via ‘systems’), making it the most relevant. The other results contain ‘database’ and are also returned.

Stopwords and Word Length

The full-text engine ignores very common words, known as stopwords (e.g., ‘the’, ‘a’, ‘is’, ‘in’), to improve performance and relevance. It also ignores words that are shorter than a certain length. The default minimum word length is controlled by the innodb_ft_min_token_size (for InnoDB) or ft_min_word_len (for MyISAM) server variables. You can customize the stopword list and minimum word length in your MySQL configuration if needed.

Example: The Effect of Stopwords

A search for ‘database and a language’ will likely produce similar results to just ‘database language’ because ‘and’ and ‘a’ are stopwords and will be ignored.

SELECT id, title FROM articles
WHERE MATCH(title, content)
AGAINST('database and a language' IN NATURAL LANGUAGE MODE);

The results will be ranked based on the presence of ‘database’ and ‘language’.

Performing a Full-Text Search from a Client Program

Here’s how to execute a full-text search from a modern application, ensuring user input is handled safely.

Syntax Overview

Node.js
Python
Java
PHP



Using `mysql2/promise` with parameterized queries to prevent SQL injection:

const query = 'SELECT ... WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)';
const [rows] = await connection.execute(query, [searchTerm]);



Using `mysql-connector-python` with parameter substitution:

query = 'SELECT ... WHERE MATCH(title, content) AGAINST(%s IN NATURAL LANGUAGE MODE)'
cursor.execute(query, (search_term,))
results = cursor.fetchall()



Using JDBC with `PreparedStatement` for security and performance:

String sql = "SELECT ... WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setString(1, searchTerm);
    ResultSet rs = pstmt.executeQuery();
    // ... process results
}



Using PDO with prepared statements:

$stmt = $pdo->prepare('SELECT ... WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)');
$stmt->execute([$searchTerm]);
$results = $stmt->fetchAll();

Complete Example (Java)

This Java example uses a PreparedStatement to safely perform a full-text search with user-provided terms.

import java.sql.*;

public class FullTextSearchExample {
    // --- Configuration ---
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_user";
    private static final String PASS = "your_password";

    public static void searchArticles(String searchTerm) {
        String sql = "SELECT id, title FROM articles " +
                     "WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)";

        System.out.println("\nSearching for articles matching: '" + searchTerm + "'\n");

        // Use try-with-resources for automatic resource management
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, searchTerm);

            try (ResultSet rs = pstmt.executeQuery()) {
                boolean found = false;
                while (rs.next()) {
                    found = true;
                    System.out.printf("ID: %d, Title: %s%n",
                            rs.getInt("id"),
                            rs.getString("title"));
                }
                if (!found) {
                    System.out.println("No matching articles found.");
                }
            }
        } catch (SQLException e) {
            System.err.println("Database error occurred:");
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        // Load the MySQL driver
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("MySQL JDBC Driver not found.");
            return;
        }
        searchArticles("database optimization");
        searchArticles("learning python");
    }
}

/*
Expected Output:

Searching for articles matching: 'database optimization'

ID: 2, Title: Advanced MySQL Techniques
ID: 4, Title: The Importance of Database Backups

Searching for articles matching: 'learning python'

ID: 3, Title: Learning a New Programming Language
*/