MySQL - Query Expansion Fulltext Search

MySQL - Full-Text Search with Query Expansion

MySQL’s Full-Text Search (FTS) is a powerful feature for performing complex text-based searches on string data. Unlike a simple LIKE comparison, FTS understands natural language, ranks results by relevance, and supports advanced search modes.

There are three primary full-text search modes in MySQL:

• Natural Language Mode: The default mode, which interprets the search string as a phrase in natural human language.
• Boolean Mode: Allows for complex queries using operators like + (required), - (exclude), and * (wildcard).
• Query Expansion Mode: A modification of natural language mode that broadens the search to include related terms, which is the focus of this tutorial.

Understanding Query Expansion

Often, a user’s search query is too short or specific, and they might miss relevant documents that use synonyms or closely related concepts. Query Expansion, also known as “blind relevance feedback,” addresses this by performing the search in two stages:

1. Stage 1: MySQL first performs a standard natural language search for the user’s original query.
2. Stage 2: It then analyzes the most relevant documents from the first stage and extracts words that appear to be highly related to the initial query. It then performs a second search using both the original query and these new, related words.

This technique helps uncover results that the user might not have thought to search for directly. It is activated by adding WITH QUERY EXPANSION to the AGAINST() function.

Modern Context: InnoDB and Dedicated Search Engines

It’s important to note that Full-Text Search is fully supported by the default InnoDB storage engine. In the past, FTS was primarily a feature of the MyISAM engine. For highly demanding, large-scale search applications, many modern systems integrate with dedicated search engines like Elasticsearch or OpenSearch. However, MySQL’s built-in FTS is incredibly capable and convenient for a wide range of applications.

Example: Searching a Tech Blog

First, let’s create a table for blog articles and add 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,
    FULLTEXT KEY ft_index (title, content)
) ENGINE=InnoDB;

Now, let’s insert some articles:

INSERT INTO articles (title, content) VALUES
('Introduction to Databases', 'A database is an organized collection of data, generally stored and accessed electronically. SQL is the standard language.'),
('Learning MySQL', 'MySQL is a popular relational database management system (RDBMS). It is a key part of the LAMP stack.'),
('Web Security Fundamentals', 'Protecting your web application is crucial. A secure database prevents data breaches.'),
('Deep Dive into RDBMS', 'Relational models are the foundation for systems like MySQL and PostgreSQL.');

Standard Search vs. Query Expansion

If a user searches for “MySQL”, a standard natural language search will find the documents that explicitly mention the term.

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

This would likely return articles 2 and 4. Now, let’s use query expansion.

SELECT id, title,
    MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION) AS relevance_score
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION)
ORDER BY relevance_score DESC;

In this case, the results might look like this:

id	title	relevance_score
2	Learning MySQL	1.8…
4	Deep Dive into RDBMS	0.9…
1	Introduction to Databases	0.8…

Notice how the search for “MySQL” also returned “Introduction to Databases”. This happened because the initial search found “Learning MySQL” and “Deep Dive into RDBMS” as highly relevant. From those articles, MySQL likely identified related terms like “database” and “RDBMS”. In the second pass, it used these new terms to find article 1, even though it doesn’t contain the word “MySQL”. We also selected the relevance_score to see how MySQL ranks the results.

Using Query Expansion in a Client Program

Here’s how you could implement this search feature in a web application.

Python Example

import mysql.connector

def expanded_search(db_config, search_term):
    query = """
        SELECT id, title, content,
               MATCH(title, content) AGAINST(%s WITH QUERY EXPANSION) AS score
        FROM articles
        WHERE MATCH(title, content) AGAINST(%s WITH QUERY EXPANSION)
        ORDER BY score DESC
    """
    try:
        with mysql.connector.connect(**db_config) as cnx:
            with cnx.cursor(dictionary=True) as cursor:
                # Use parameterized query for safety
                cursor.execute(query, (search_term, search_term))
                results = cursor.fetchall()

                print(f"Expanded search results for: '{search_term}'")
                for row in results:
                    print(f"  - ID: {row['id']}, Title: {row['title']}, Score: {row['score']:.4f}")

    except mysql.connector.Error as err:
        print(f"Database Error: {err}")

# Example usage
db_config = {'user': 'root', 'password': 'password', 'host': '127.0.0.1', 'database': 'your_db'}
expanded_search(db_config, 'MySQL')

Node.js (with mysql2) Example

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

async function expandedSearch(dbConfig, searchTerm) {
    let connection;
    const query = `
        SELECT id, title,
               MATCH(title, content) AGAINST(? WITH QUERY EXPANSION) AS score
        FROM articles
        WHERE MATCH(title, content) AGAINST(? WITH QUERY EXPANSION)
        ORDER BY score DESC
    `;
    try {
        connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute(query, [searchTerm, searchTerm]);

        console.log(`Expanded search results for: '${searchTerm}'`);
        rows.forEach(row => {
            console.log(`  - ID: ${row.id}, Title: ${row.title}, Score: ${row.score.toFixed(4)}`);
        });

    } catch (err) {
        console.error(`Database Error: ${err.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

// Example usage
const dbConfig = { host: 'localhost', user: 'root', password: 'password', database: 'your_db' };
expandedSearch(dbConfig, 'MySQL');