MySQL - ngram Fulltext Parser

MySQL - ngram Full-Text Parser for CJK Languages

Standard full-text search in MySQL relies on delimiters like spaces and punctuation to separate words (tokens). This works well for languages like English, but fails for ideographic languages such as Chinese, Japanese, and Korean (CJK), which often do not use spaces between words. To enable effective searching in these languages, MySQL provides the ngram full-text parser.

What is an ngram?

An ngram is a contiguous sequence of ‘n’ characters from a string of text. The ngram parser tokenizes text by sliding a window of ‘n’ characters across it. The value of ‘n’ is configurable.

For example, tokenizing the Japanese word “東京” (Tokyo) with different ‘n’ values:

n=1: '東', '京' (unigrams)
n=2: '東京' (bigram)

Tokenizing the Chinese phrase “搜索引擎” (Search Engine):

n=2 (bigrams): '搜索', '索引', '引擎'

Configuring the ngram Token Size

The token size is controlled by the ngram_token_size server variable. The default is 2 (bigrams), and it can be set from 1 to 10. A smaller token size results in a smaller index and faster searches, but may yield less relevant results. This variable is read-only at runtime and must be set in your MySQL configuration file (my.cnf or my.ini).

[mysqld]
ngram_token_size=2

You must restart the MySQL server for this change to take effect. After changing ngram_token_size, you must rebuild any FULLTEXT indexes that use the ngram parser.

Creating a FULLTEXT Index with the ngram Parser

To use the parser, you specify WITH PARSER ngram when creating your FULLTEXT index. It is crucial that your table and columns use a Unicode character set like utf8mb4.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    FULLTEXT (title, content) WITH PARSER ngram
) ENGINE=InnoDB CHARACTER SET=utf8mb4;

Example Data

Let’s insert some sample articles in Japanese.

INSERT INTO articles (title, content) VALUES
('日本の首都', '日本の首都は東京です。'),
('京都の観光', '京都には多くの有名な寺院があります。');

How ngram Search Works

When you perform a search, the search query itself is also tokenized into ngrams.

Natural Language Mode Search

A search for “東京” (with ngram_token_size=2) becomes a search for the token '東京'. This will match the first article.

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('東京' IN NATURAL LANGUAGE MODE);

Boolean Mode Search

Boolean mode provides more control. A search for a phrase is converted into a search for the sequence of ngrams. For example, a search for "京都 寺院" (Kyoto Temple) becomes a search for documents containing the ngrams '京都', '都 , ' 寺', '寺院' in that proximity.

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('"京都の寺院"' IN BOOLEAN MODE);

This would likely match the second article, as it contains the sequence of characters.

Important Considerations

• Space Handling: The ngram parser treats spaces as delimiters. It removes them and does not generate ngrams that span across a space. For example, "ab cd" is parsed into ngrams for "ab" and "cd" separately.
• Stopword Handling: Common words (stopwords) are removed before tokenization. If a token is on the stopword list, it is not indexed. This applies to CJK languages as well if you have defined a relevant stopword list.
• Wildcard Search: Wildcard searches (*) can be unintuitive. If the prefix is shorter than ngram_token_size, it may return many results. If it’s longer, the wildcard operator is often ignored and the term is converted to an ngram phrase search.

Using ngram Parser in a Client Program (Node.js Example)

Here’s how you might implement a search feature in a Node.js application using mysql2.

// searchService.js
const pool = require('./db'); // Assumes a configured mysql2 promise pool

async function searchArticles(query) {
    // Use BOOLEAN MODE for more precise phrase searching.
    // Add wildcards to allow for partial matches at the end.
    const searchQuery = `"${query}*"`;

    const sql = `
        SELECT id, title, content
        FROM articles
        WHERE MATCH(title, content) AGAINST(? IN BOOLEAN MODE);`;

    try {
        const [rows] = await pool.execute(sql, [searchQuery]);
        console.log(`Found ${rows.length} results for '${query}':`);
        console.log(rows);
        return rows;
    } catch (error) {
        console.error('Full-text search failed:', error);
        throw error;
    }
}

// Example usage:
// searchArticles('東京');
// searchArticles('京都 観光');