FULLTEXT Indexes, Tokenization, Boolean/Natural Language Search, and Limits

Learning outcomes

ServiceHub operators search incident notes with phrases such as “packet loss router” and expect relevant records rather than an expensive LIKE '%packet loss%' scan. A FULLTEXT index is designed for token-based text search, but it is not a substring index and its results depend on storage engine, parser/tokenization rules, stopwords, minimum token length, collation and search mode. Treating it as a magical replacement for every text-search system produces surprising omissions.

1. Build an InnoDB FULLTEXT index and make token search observable

USE servicehub_index_lab;CREATE TABLE knowledge_articles (  article_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  title VARCHAR(180) NOT NULL,  body TEXT NOT NULL,  FULLTEXT KEY ft_title_body(title,body)) ENGINE=InnoDB;INSERT INTO knowledge_articles(title,body) VALUES ('Diagnose packet loss','Measure loss, latency and interface errors before replacing the router.'), ('Router reboot loop','Inspect power, temperature and firmware logs before rebooting again.'), ('Fiber attenuation','Measure optical power and clean connectors before escalation.'), ('VPN authentication','Check certificate expiry and MFA synchronization for remote users.'), ('Packet capture workflow','Capture packets near the failing interface and record timestamps.');SHOW CREATE TABLE knowledge_articles\GSHOW INDEX FROM knowledge_articles;

SELECT article_id,title,       MATCH(title,body) AGAINST ('packet router' IN NATURAL LANGUAGE MODE) AS relevanceFROM knowledge_articlesWHERE MATCH(title,body) AGAINST ('packet router' IN NATURAL LANGUAGE MODE)ORDER BY relevance DESC, article_id;EXPLAINSELECT article_id,titleFROM knowledge_articlesWHERE MATCH(title,body) AGAINST ('packet router' IN NATURAL LANGUAGE MODE);

Natural-language mode evaluates indexed terms and produces a relevance value. Relevance is an engine/search-mode score, not a universal semantic probability. Use it to order candidates in this search contract, and validate whether the ranking actually serves the application’s users.

2. Boolean mode exposes explicit term requirements

SELECT article_id,title,       MATCH(title,body) AGAINST ('+packet +router -vpn' IN BOOLEAN MODE) AS scoreFROM knowledge_articlesWHERE MATCH(title,body) AGAINST ('+packet +router -vpn' IN BOOLEAN MODE)ORDER BY score DESC, article_id;

Boolean mode adds operators for required, excluded, weighted and wildcard-like token matching. Its syntax is useful for operator-facing search boxes, but exposing raw boolean syntax to end users can create usability and escaping concerns. If application input is converted into boolean syntax, treat it as its own parser/validation problem rather than concatenating arbitrary text into SQL.

3. Deliberately wrong: expect FULLTEXT to implement arbitrary substring matching

-- A user wants any body containing the character substring "atten".SELECT article_id,titleFROM knowledge_articlesWHERE MATCH(title,body) AGAINST ('atten' IN NATURAL LANGUAGE MODE);-- Compare the literal substring semantics (potentially scan-heavy):SELECT article_id,titleFROM knowledge_articlesWHERE body LIKE '%atten%';

FULLTEXT indexes work with parsed tokens, not arbitrary character substrings. Searching for a fragment inside a longer token can return nothing even though LIKE finds a row. Boolean-mode wildcard behavior can extend token prefixes in supported cases, but it still does not transform FULLTEXT into a general n-gram substring engine. The repair is to define the actual search requirement first: token search, prefix completion, fuzzy matching, CJK segmentation, phrase ranking or arbitrary substring search are different problems.

4. Stopwords and minimum token length belong to the index contract

SHOW VARIABLES LIKE 'ft_min_word_len';SHOW VARIABLES LIKE 'ft_stopword_file';SHOW VARIABLES LIKE 'innodb_ft_min_token_size';SHOW VARIABLES LIKE 'innodb_ft_enable_stopword';SHOW VARIABLES LIKE 'innodb_ft_server_stopword_table';

MariaDB distinguishes legacy MyISAM FULLTEXT variables such as ft_min_word_len from InnoDB FULLTEXT variables such as innodb_ft_min_token_size. Stopword configuration is also engine-specific. If you change token-size or stopword settings, existing FULLTEXT indexes do not magically reinterpret their old token set; plan and test the required rebuild/reindex procedure for the exact engine and version.

5. Engine choice and multilingual search require explicit verification

InnoDB gives ServiceHub the transactional storage model used elsewhere in the course and includes built-in FULLTEXT. MyISAM and Aria also support FULLTEXT with different transaction/locking/recovery contracts. Mroonga is an optional Groonga-based storage engine designed for full-text use cases including CJK-ready search, but its plugin/package/platform prerequisites must be verified before it enters an architecture decision.

This is a feature-and-correctness comparison, not a claim that one engine “scales better.” Benchmark your corpus, query mix, update rate and result-quality requirements. Search quality is as important as query latency.

6. Rebuild and migration discipline

CREATE TABLE bulk_articles (  article_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,  title VARCHAR(180) NOT NULL,  body TEXT NOT NULL) ENGINE=InnoDB;INSERT INTO bulk_articles SELECT article_id,title,body FROM knowledge_articles;ALTER TABLE bulk_articles ADD FULLTEXT KEY ft_bulk(title,body);CHECK TABLE bulk_articles;

MariaDB documentation notes that for large data sets it can be faster to load data before creating the FULLTEXT index. That is a workload-dependent operational pattern, not a promise for every dataset. For production migrations, measure build duration, disk/temp-space needs, lock/DDL behavior, replica/Galera implications and recovery plan before applying the change to a busy table.

7. Verification checklist, production judgment, and bridge

1. Verify the table engine and that indexed columns use a FULLTEXT-supported string type.
2. Record effective token-size and stopword variables for the actual engine.
3. Test natural-language and boolean queries against a labeled set of expected results.
4. Include intentionally short, stopword-like, multilingual and substring cases to expose boundaries.
5. Plan index rebuild behavior before changing parser/token settings.
6. Decide whether search belongs inside MariaDB or in a separate search service by requirements and operational cost.

Next, spatial access paths replace token matching with geometry relationships. The most important new distinction is between an R-tree bounding-box access path and the exact shape predicate used to confirm geometric truth.