Full-Text Search

Full-text search finds rows containing words or phrases in text columns, ranked by relevance, using a FULLTEXT index (InnoDB and MyISAM).

Concept

Full-text search finds rows containing words or phrases in text columns, ranked by relevance, using a FULLTEXT index (InnoDB and MyISAM). Queries use MATCH (cols) AGAINST ('terms' [mode]) in natural-language, boolean, or query-expansion mode. The built-in ngram parser supports Chinese, Japanese, and Korean text.

Why It Matters

LIKE '%word%' cannot use normal indexes and does not rank results. Full-text indexes make keyword search over defect reports, test logs, knowledge-base articles, and product descriptions fast.

Syntax

CREATE FULLTEXT INDEX ft_name ON tbl (col1, col2) [WITH PARSER ngram];
SELECT ... WHERE MATCH (col1, col2) AGAINST ('search terms' [IN NATURAL LANGUAGE MODE | IN BOOLEAN MODE | WITH QUERY EXPANSION]);

Example

Key rules (InnoDB):

Minimum indexed word length innodb_ft_min_token_size = 3 (words like "V" or "Nm" are not indexed); maximum 84.

A default stopword list excludes common words ("the", "and"); custom lists via innodb_ft_server_stopword_table.

Natural-language mode ignores words that appear in 50% or more of rows only for MyISAM; InnoDB ranks with a TF-IDF-style algorithm.

The MATCH column list must exactly match a FULLTEXT index's columns.

After changing token-size or stopword settings, rebuild the index.

CREATE TABLE defect_reports (
    defect_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    description TEXT         NOT NULL,
    FULLTEXT INDEX ft_defects (title, description)
) ENGINE = InnoDB;

INSERT INTO defect_reports (title, description) VALUES
('Regen braking torque exceeds limit', 'During HIL test TC-VCU-002 regen torque reached 270 Nm above the 250 Nm limit.'),
('BMS cell overvoltage not detected', 'Cell voltage 4.31 V was not flagged; overvoltage protection failed on BMS 2.3.1.'),
('CAN frame loss on VCU_Status', 'Frame loss of 5 percent observed on PT-CAN during vehicle test.'),
('Door ajar switch intermittent', 'BCM reports B1234 intermittently when driver door is closed.');

-- Natural language mode (ranked)
SELECT defect_id, title,
       ROUND(MATCH (title, description) AGAINST ('overvoltage protection'), 4) AS score
FROM defect_reports
WHERE MATCH (title, description) AGAINST ('overvoltage protection')
ORDER BY score DESC;
-- returns defect 2 only (the only row containing those words)

-- Boolean mode: + required, - excluded, * prefix wildcard, "phrase"
SELECT defect_id, title FROM defect_reports
WHERE MATCH (title, descriptio
…

CREATE FULLTEXT INDEX ft_title_ngram ON articles (title) WITH PARSER ngram;   -- ngram_token_size default 2

Expected Result

(BCM in defect 4 is a different word; BMS has 3 characters so it is indexed.)

SELECT defect_id, title FROM defect_reports
WHERE MATCH (title, description) AGAINST ('BMS' IN BOOLEAN MODE);

+-----------+------------------------------------+
| defect_id | title                              |
+-----------+------------------------------------+
|         2 | BMS cell overvoltage not detected  |
+-----------+------------------------------------+

Common Mistakes

Searching for words shorter than the minimum token size.

MATCH column list not matching the index.

Expecting LIKE-style substring matching (full-text matches words and prefixes, not arbitrary substrings).

Using full-text for very high-volume search where a dedicated engine (Elasticsearch/OpenSearch) is more suitable.

Best Practices

Use boolean mode for user-facing search boxes with explicit operators.

Tune token size and stopwords for technical vocabularies (codes like "ECU", "DTC").

Combine with normal filters (AND status = 'OPEN').

Interview Relevance

Low-medium: "How do you implement search in MySQL?", "Natural vs boolean mode?", "Why doesn't my full-text search find short words?".

Practice Questions

Search defect reports containing "CAN" but not "loss".

Explain why searching "V" finds nothing.

Rank reports for the query "test limit".