MySQL - Fulltext Search

MySQL - Modern Full-Text Search

Introduction to Full-Text Search (FTS)

Full-Text Search (FTS) in MySQL provides a powerful way to run complex text-based queries against character data stored in your database. Unlike simple LIKE pattern matching, FTS is optimized for natural language, allowing you to find records that are most relevant to a phrase, not just those that contain an exact substring.

To enable FTS on one or more columns, you must first create a FULLTEXT index on them. This index tokenizes the text content, creating an efficient lookup structure for searching words and phrases.

Storage Engines: InnoDB vs. MyISAM

MySQL supports FULLTEXT indexes on tables using either the InnoDB or MyISAM storage engines. However, InnoDB has been the default storage engine since MySQL 5.5 and is recommended for virtually all use cases due to its support for transactions, foreign keys, and crash recovery.

• InnoDB: The modern, default choice. It supports full-text search since MySQL 5.6. It is a transactional, crash-safe engine and should be your primary choice.
• MyISAM: A legacy storage engine. While it has historically been known for FTS, it lacks the reliability and features of InnoDB. You should only use it if you have a specific, legacy reason to do so.

This tutorial will focus exclusively on using FTS with the InnoDB storage engine.

Creating a FULLTEXT Index

You can add a FULLTEXT index when creating a table or add it to an existing table. For large datasets, it’s significantly faster to load the data first and then create the index.

1. During Table Creation (CREATE TABLE)

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

2. On an Existing Table (ALTER TABLE)

ALTER TABLE articles
ADD FULLTEXT KEY ft_title_content (title, content);

3. Using CREATE INDEX

CREATE FULLTEXT INDEX ft_title_content
ON articles(title, content);

Performing Full-Text Searches

Searches are performed using the MATCH() and AGAINST() functions within a WHERE or ORDER BY clause. MATCH() specifies the indexed columns to search, and AGAINST() provides the search query.

SELECT title, content, MATCH(title, content) AGAINST('search query') AS relevance_score
FROM articles
WHERE MATCH(title, content) AGAINST('search query');

Let’s populate our articles table and explore the different search modes.

INSERT INTO articles (title, content)
VALUES
('Introduction to SQL', 'SQL is a powerful language for database management.'),
('Modern JavaScript Features', 'This article covers modern features like async/await in JavaScript.'),
('Database Indexing Explained', 'Indexing is crucial for database performance and fast queries.'),
('A Guide to SQL Joins', 'Learn how to combine rows from two or more tables using SQL.');

Natural Language Mode (Default)

This mode interprets the search string as a natural phrase. It finds rows that are relevant to the query. Results are automatically sorted by relevance, with higher relevance scores appearing first.

-- Search for articles about 'SQL database'
SELECT title, MATCH(title, content) AGAINST('SQL database' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('SQL database' IN NATURAL LANGUAGE MODE);

Output

title	score
Introduction to SQL	1.312…
Database Indexing Explained	0.678…
A Guide to SQL Joins	0.634…

Boolean Mode

Boolean mode allows for more complex searches using special operators. This mode does not automatically sort by relevance.

• +: The word MUST be present. +SQL -Joins
• -: The word MUST NOT be present. database -performance
• >: Increases the word’s contribution to the relevance score. <: Decreases it.
• *: Wildcard for words starting with a prefix. data* matches ‘database’, ‘data’.

-- Find articles containing 'SQL' but NOT 'Joins'
SELECT title
FROM articles
WHERE MATCH(title, content) AGAINST('+SQL -Joins' IN BOOLEAN MODE);

Output

title
Introduction to SQL

Query Expansion Mode

This is an advanced mode that broadens the search. It performs a natural language search first, then adds the most relevant words from the initial results to the search string and performs the search again. It’s useful for finding related concepts.

-- Search for 'database' and related terms
SELECT title
FROM articles
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);

Dropping a FULLTEXT Index

To remove a FULLTEXT index, you use the DROP INDEX command with ALTER TABLE. You must know the index’s name.

-- Find the index name first if you don't know it
SHOW CREATE TABLE articles;

-- Drop the index
ALTER TABLE articles DROP INDEX ft_title_content;

Performance Considerations and Best Practices

• Indexing Strategy: Only index columns that you will actually search. Including unnecessary columns bloats the index and slows down writes.
• Stopwords: MySQL ignores very common words (like ‘the’, ‘is’, ‘a’), known as stopwords. You can customize the stopword list if needed.
• Minimum Word Length: By default, InnoDB only indexes words that are 3 or more characters long (innodb_ft_min_token_size). If you need to search for shorter words, you must change this server configuration and rebuild your indexes.
• Alternative Solutions: For very large-scale or complex search requirements (e.g., faceting, geo-search, aggregations), dedicated search engines like Elasticsearch or OpenSearch are superior. MySQL FTS is excellent for moderate, integrated search needs.

Common Errors and Debugging

• Error 1191: Can’t find FULLTEXT index…: This means you are trying to use MATCH() ... AGAINST() on columns that do not have a FULLTEXT index defined, or the columns in MATCH() do not exactly match the columns in an index definition.
• No results returned: Check the minimum word length. If you search for ‘js’ and the minimum length is 3, you won’t get any results. Also, words present in more than 50% of the rows are considered stopwords by default and are ignored in natural language searches.

Connecting with Client Applications (Modern Examples)

Here’s how to perform a full-text search from modern programming environments, using best practices like asynchronous operations and prepared statements.

Node.js
Python
PHP
Java



// Using mysql2/promise for async/await
// npm install mysql2

const mysql = require('mysql2/promise');

async function searchArticles(searchTerm) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'TUTORIALS'
    });

    const query = `
      SELECT title, MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE) AS score
      FROM articles
      WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)
      ORDER BY score DESC;
    `;

    const [rows] = await connection.execute(query, [searchTerm, searchTerm]);
    console.log(`Search results for '${searchTerm}':`);
    console.log(rows);
    return rows;
  } catch (error) {
    console.error('An error occurred:', error);
  } finally {
    if (connection) await connection.end();
  }
}

searchArticles('SQL database');




# Using mysql-connector-python and context managers
# pip install mysql-connector-python

import mysql.connector
from mysql.connector import errorcode

def search_articles(search_term):
    config = {
        'user': 'root',
        'password': 'password',
        'host': 'localhost',
        'database': 'TUTORIALS'
    }
    query = ("""
        SELECT title, MATCH(title, content) AGAINST(%s IN NATURAL LANGUAGE MODE) AS score
        FROM articles
        WHERE MATCH(title, content) AGAINST(%s IN NATURAL LANGUAGE MODE)
        ORDER BY score DESC
    """)

    try:
        with mysql.connector.connect(**config) as connection:
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(query, (search_term, search_term))
                results = cursor.fetchall()
                print(f"Search results for '{search_term}':")
                for row in results:
                    print(row)
                return results
    except mysql.connector.Error as err:
        print(f"An error occurred: {err}")

search_articles('SQL database')




// Using PDO for secure, prepared statements

<?php
$host = 'localhost';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

function searchArticles(string $searchTerm, PDO $pdo): array
{
    $query = <<<'SQL'
        SELECT title, MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE) AS score
        FROM articles
        WHERE MATCH(title, content) AGAINST(? IN NATURAL LANGUAGE MODE)
        ORDER BY score DESC
    SQL;

    try {
        $stmt = $pdo->prepare($query);
        $stmt->execute([$searchTerm, $searchTerm]);
        return $stmt->fetchAll();
    } catch (PDOException $e) {
        throw new PDOException($e->getMessage(), (int)$e->getCode());
    }
}

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    $results = searchArticles('SQL database', $pdo);
    echo "Search results for 'SQL database':\n";
    print_r($results);
} catch (PDOException $e) {
     die("DB ERROR: " . $e->getMessage());
}




// Using modern JDBC with try-with-resources

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FullTextSearchExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

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

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, searchTerm);
            pstmt.setString(2, searchTerm);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("Search results for '" + searchTerm + "':");
                while (rs.next()) {
                    System.out.printf("Title: %s, Score: %f%n",
                            rs.getString("title"),
                            rs.getFloat("score"));
                }
            }
        } catch (SQLException e) {
            System.err.println("SQL Error: " + e.getMessage());
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        searchArticles("SQL database");
    }
}