FULLTEXT Search, Tokenization, Relevance, Stopwords, and Search-System Boundaries

Learning outcomes

ServiceHub technicians search repair notes and knowledge articles using phrases such as “pressure sensor drift” or “robot joint calibration.” A LIKE '%sensor%' scan is easy to write but does not provide token-based relevance and usually cannot use a normal B-tree for the leading-wildcard pattern. MySQL FULLTEXT search can solve a useful middle class of search problems—but only if learners understand tokenization, stopwords, modes, index build configuration, and the boundary where a dedicated search engine becomes the better architecture.

Build a deterministic knowledge-base dataset

USE servicehub_special_lab;DROP TABLE IF EXISTS knowledge_articles;CREATE TABLE knowledge_articles (  article_id BIGINT PRIMARY KEY,  title VARCHAR(180) NOT NULL,  body TEXT NOT NULL,  status ENUM('draft','published') NOT NULL DEFAULT 'published',  FULLTEXT KEY ft_article (title,body)) ENGINE=InnoDB;INSERT INTO knowledge_articles VALUES(1,'Pressure sensor drift diagnosis', 'Calibrate the pressure sensor, compare the reference gauge, and inspect temperature compensation.','published'),(2,'Pump vibration inspection', 'Measure vibration spectrum, bearing temperature, alignment, and rotating equipment condition.','published'),(3,'Robot joint calibration', 'Calibrate encoder offsets and verify robot joint limits after controller maintenance.','published'),(4,'Battery inspection with AI note', 'AI assisted review is experimental; inspect state of charge, cycles, connectors, and temperature first.','published'),(5,'Obsolete pressure sensor procedure', 'This obsolete procedure is retained only for historical audit context.','published');SHOW INDEX FROM knowledge_articles;SHOW VARIABLES LIKE 'innodb_ft_min_token_size';SHOW VARIABLES LIKE 'innodb_ft_max_token_size';SHOW VARIABLES LIKE 'innodb_ft_enable_stopword';

For InnoDB, the default minimum token length is normally three characters, but the lesson reads the variable instead of assuming a default. Stopwords are also part of index construction. These settings matter because a query can be syntactically correct while the desired token was never indexed.

Natural-language mode returns relevance scores

SELECT article_id,title,       MATCH(title,body)       AGAINST('pressure sensor' IN NATURAL LANGUAGE MODE) AS scoreFROM knowledge_articlesWHERE MATCH(title,body)      AGAINST('pressure sensor' IN NATURAL LANGUAGE MODE)ORDER BY score DESC, article_id;-- Compare the leading-wildcard fallback shape.EXPLAIN FORMAT=TREESELECT article_id,titleFROM knowledge_articlesWHERE body LIKE '%pressure sensor%';

The score is meaningful for ranking within this query and corpus; do not treat it as a stable probability. As the corpus changes, document frequencies and relevance can change. Natural-language FULLTEXT search is token-oriented, not a general substring engine.

Boolean mode expresses required, excluded, and optional terms

SELECT article_id,title,       MATCH(title,body)       AGAINST('+pressure +sensor -obsolete' IN BOOLEAN MODE) AS scoreFROM knowledge_articlesWHERE MATCH(title,body)      AGAINST('+pressure +sensor -obsolete' IN BOOLEAN MODE)ORDER BY score DESC, article_id;

Boolean mode is useful for explicit operator logic, but it is not a full domain-specific ranking language. The application must still define safe query construction, expected semantics, result limits, and user-facing behavior when no documents match.

Failure case: “AI” exists in text but may not be searchable

SELECT @@innodb_ft_min_token_size AS min_token_chars,       @@innodb_ft_enable_stopword AS stopwords_enabled;SELECT article_id,title,       MATCH(title,body) AGAINST('AI' IN NATURAL LANGUAGE MODE) AS scoreFROM knowledge_articlesWHERE MATCH(title,body) AGAINST('AI' IN NATURAL LANGUAGE MODE);SELECT valueFROM information_schema.INNODB_FT_DEFAULT_STOPWORDWHERE value IN ('the','and','is')ORDER BY value;

If innodb_ft_min_token_size is 3, the two-character token AI is normally excluded from the InnoDB FULLTEXT index, so a zero-result search is expected even though row 4 contains the letters. The wrong fix is to change a global startup setting on production immediately. Changing token-size or stopword policy affects index construction and requires deliberate FULLTEXT index rebuild/revalidation. First prove the product requirement and corpus impact.

Changing token policy is an index migration, not a query-only tweak

For InnoDB FULLTEXT, minimum/maximum token size and stopword policy affect what is stored in the index. If product requirements truly demand two-character tokens or a custom stopword set, treat the change like a schema/index migration: test the corpus impact, change the relevant server configuration, restart when a non-dynamic variable requires it, rebuild the FULLTEXT index, and re-run both relevance and resource acceptance tests. A configuration edit without an index rebuild leaves old tokenization semantics in the existing index.

-- Do not run this merely to make the tutorial search for 'AI'.-- After configuration is approved/applied and, when required, server restarted:ALTER TABLE knowledge_articles  DROP INDEX ft_knowledge,  ADD FULLTEXT INDEX ft_knowledge(title, body);SHOW INDEX FROM knowledge_articles;-- Re-run the exact acceptance queries and compare result sets/scores.SELECT article_id,title,       MATCH(title,body) AGAINST('pressure sensor' IN NATURAL LANGUAGE MODE) AS scoreFROM knowledge_articlesWHERE MATCH(title,body) AGAINST('pressure sensor' IN NATURAL LANGUAGE MODE)ORDER BY score DESC, article_id;

Rebuilding is potentially expensive on a large table and also changes the search corpus behavior. Therefore the acceptance test must include precision (how many returned results are useful), recall (how many relevant results are found), ranking stability for important queries, build duration/space, replication impact where applicable, and rollback/rebuild instructions. Query latency alone is not enough to judge a search change.

Tokenization is language-sensitive

The built-in parser identifies word boundaries from delimiters. Languages where words are not naturally separated in that way need another approach; MySQL provides parser plugins such as ngram for Chinese, Japanese, or Korean scenarios. This is already a clue about the boundary: search quality is a linguistic product, not merely an index type.

Observe what the server proves

SELECT INDEX_NAME,INDEX_TYPE,COLUMN_NAME,SEQ_IN_INDEXFROM information_schema.STATISTICSWHERE TABLE_SCHEMA='servicehub_special_lab'  AND TABLE_NAME='knowledge_articles'ORDER BY INDEX_NAME,SEQ_IN_INDEX;SELECT DIGEST_TEXT,COUNT_STAR,       ROUND(SUM_TIMER_WAIT/1000000000000,6) AS total_secondsFROM performance_schema.events_statements_summary_by_digestWHERE SCHEMA_NAME='servicehub_special_lab'  AND DIGEST_TEXT LIKE '%MATCH%AGAINST%'ORDER BY SUM_TIMER_WAIT DESCLIMIT 10;

Performance Schema digests can show that search is occurring and how much time it consumed in this server since instrumentation/reset boundaries. It does not tell you whether search relevance satisfied users. Operational performance and information-retrieval quality require different evidence.

Production judgment: measure search quality, not only query latency

A search feature should have a small labeled evaluation set: representative queries, expected relevant articles, unacceptable results, and ranking expectations. Then measure retrieval quality alongside latency, index-build cost, write amplification, storage, and operational complexity. A fast query that returns the wrong technician procedure is not successful search.

-- Expected to find the pressure-sensor article.SELECT COUNT(*) >= 1 AS pressure_search_has_resultFROM knowledge_articlesWHERE MATCH(title,body)      AGAINST('pressure sensor' IN NATURAL LANGUAGE MODE);-- Expected Boolean exclusion: obsolete procedure should not be returned.SELECT COUNT(*) = 0 AS obsolete_is_excludedFROM knowledge_articlesWHERE article_id=5  AND MATCH(title,body)      AGAINST('+pressure +sensor -obsolete' IN BOOLEAN MODE);

Authoritative references

• MySQL Community Server 8.4 Downloads
• MySQL NDB Cluster 8.4 Downloads
• MySQL 8.4 — NDB Cluster
• MySQL 8.4 — InnoDB Compared with NDB Cluster
• MySQL 8.4 — Using MySQL as a Document Store
• MySQL Shell 8.4
• MySQL X DevAPI User Guide
• MySQL 8.4 — Spatial Analysis Functions
• MySQL 8.4 — Full-Text Search