MySQL Full-Text Search: A DIY Implementation Instead of External Search

1. What a FULLTEXT index in MySQL actually does

A FULLTEXT index in MySQL breaks text columns into individual words, removes stopwords, and builds an inverted index structure that maps words to the rows they occur in. This allows queries that return results ranked by relevance, instead of searching only for exact matches like LIKE '%word%' does. The decisive difference to LIKE is that a FULLTEXT index actually uses an index and does not have to scan the entire table on every query.

A FULLTEXT index is created with FULLTEXT(column1, column2) in CREATE TABLE or afterward with ALTER TABLE ... ADD FULLTEXT. The search itself happens via the function MATCH(columns) AGAINST(search term), which returns a relevance score and can be used both in the SELECT list and in the WHERE clause. This approach has been available for InnoDB since MySQL 5.6, no longer limited to the older MyISAM engine.

For many applications with a manageable data volume, such as product catalogs, blog archives, or internal document search, a well configured FULLTEXT index fully covers the requirements without needing to operate, synchronize, and monitor a separate search system. The decision for or against a DIY MySQL full-text search implementation should be based on concrete requirements, not on the assumption that Elasticsearch is inherently superior.

2. Natural language mode: relevance-based search

Natural language mode is the default mode of MATCH AGAINST and interprets the search term as natural language text. MySQL breaks the search string into words, searches for rows containing at least one of these words, and calculates a relevance score for each row based on the frequency of the search words in the row relative to their frequency in the entire table. Words appearing in almost every row contribute less to the score than rare, specific words.

An important detail of natural language mode: words appearing in more than fifty percent of a table's rows are treated as stopword-like in relevance calculation and do not contribute to the score, even if they are not classic stopwords. This fifty percent threshold explains some surprising results on small test tables but disappears in practice with realistic data volumes and sufficient word variety.

CREATE TABLE articles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;

-- Natural language search, ranked by relevance
SELECT id, title,
       MATCH(title, body) AGAINST('mysql performance tuning') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('mysql performance tuning')
ORDER BY relevance DESC
LIMIT 10;

3. Boolean mode: precise search operators

Boolean mode, activated with IN BOOLEAN MODE, allows explicit operators for precise search queries. A leading plus +word forces the word to be present, a minus -word excludes rows containing this word, quotation marks define an exact phrase, and a trailing asterisk word* activates a prefix search. These operators give applications the ability to express complex search filters like "must contain X and Y but not Z" without multiple separate queries.

An essential difference from natural language mode: in boolean mode the fifty percent threshold rule does not apply, and no automatic relevance sorting is enforced, even though MATCH AGAINST still returns a score that can be used in a custom ORDER BY clause. Boolean mode is especially suited for search interfaces with advanced filter options, while natural language mode is the better default choice for simple free-text search fields.

-- Must contain "mysql", must not contain "oracle", prefix match on "index*"
SELECT id, title FROM articles
WHERE MATCH(title, body)
  AGAINST('+mysql -oracle +index*' IN BOOLEAN MODE);

-- Exact phrase search
SELECT id, title FROM articles
WHERE MATCH(title, body)
  AGAINST('"composite index design"' IN BOOLEAN MODE);

-- Higher weight for "performance", lower for "tuning" via > and <
SELECT id, title FROM articles
WHERE MATCH(title, body)
  AGAINST('+mysql >performance <tuning' IN BOOLEAN MODE);

4. Stopwords and minimum word length

MySQL uses a built-in stopword list by default with common words like "the", "and", or "of", excluded from indexing because they offer no distinguishing value for search. For German-language content, this default list is insufficient, since it primarily contains English stopwords. Via the system variable innodb_ft_server_stopword_table, you can specify a custom stopword table containing German filler words like "der", "die", "das", or "und".

Equally important is the minimum word length, controlled via innodb_ft_min_token_size, three characters by default. Short but relevant search terms such as product codes or abbreviations below this length are otherwise not indexed at all. After changing these configuration values, the affected FULLTEXT index must be rebuilt with ALTER TABLE ... ADD FULLTEXT or OPTIMIZE TABLE for the new settings to take effect.

-- Custom stopword table for German content
CREATE TABLE de_stopwords (value VARCHAR(30)) ENGINE=InnoDB;
INSERT INTO de_stopwords (value) VALUES
  ('der'), ('die'), ('das'), ('und'), ('oder'), ('ist'), ('mit');

SET GLOBAL innodb_ft_server_stopword_table = 'shop/de_stopwords';
SET GLOBAL innodb_ft_min_token_size = 2;

-- Rebuild the fulltext index so new settings take effect
ALTER TABLE articles DROP INDEX ft_title_body;
ALTER TABLE articles ADD FULLTEXT KEY ft_title_body (title, body);

5. Understanding relevance scoring with MATCH AGAINST

The relevance score returned by MATCH AGAINST is fundamentally based on a TF-IDF-like calculation: term frequency, meaning how often a search word appears in the row, multiplied by inverse document frequency, meaning how rare the word is in the entire table. Rows with many occurrences of rare search words receive higher scores than rows with few occurrences of common words. This score is a relative value without a fixed upper bound and is primarily suited for sorting, not as an absolute quality metric.

In practice, the pure relevance score is often combined with other signals like recency or popularity, using the MATCH AGAINST value as one of several factors in a weighted sort formula. For simple use cases, direct sorting by relevance is sufficient, while for product search with business logic, a combination of text relevance and other ranking factors like stock level or sales figures is often worthwhile.

-- Combine text relevance with recency and popularity into one ranking
SELECT id, title,
       MATCH(title, body) AGAINST('mysql performance') AS text_score,
       (MATCH(title, body) AGAINST('mysql performance') * 0.7)
         + (view_count / 1000 * 0.2)
         + (DATEDIFF(NOW(), created_at) < 30) * 0.1 AS final_score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql performance')
ORDER BY final_score DESC
LIMIT 10;

6. The ngram parser for CJK languages

The default FULLTEXT parser in MySQL is based on word boundaries defined by whitespace and punctuation. This assumption works well for European languages but fails for Chinese, Japanese, and Korean text, CJK for short, because these languages do not use explicit word boundaries via whitespace. For this case, MySQL offers the ngram parser, which breaks text into overlapping character sequences of fixed length, two characters by default, instead of relying on whitespace.

The ngram parser is explicitly activated with WITH PARSER ngram when creating the index and configured via the system variable ngram_token_size. An ngram size of two characters is a good compromise for most CJK text, larger values increase search precision but noticeably enlarge the index, since more overlapping character sequences must be stored. For mixed-language content with both European and Asian text, a combination of two separate FULLTEXT indexes with different parsers can be useful.

-- ngram parser for CJK content: no whitespace word boundaries needed
SET GLOBAL ngram_token_size = 2;

CREATE TABLE articles_cjk (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT KEY ft_body_ngram (body) WITH PARSER ngram
) ENGINE=InnoDB;

SELECT id, title FROM articles_cjk
WHERE MATCH(body) AGAINST('数据库性能' IN NATURAL LANGUAGE MODE);

7. FULLTEXT on InnoDB versus MyISAM

Before MySQL 5.6, full-text search was exclusively available on the MyISAM engine, which frequently forced developers to choose between transactional safety and full-text search. Since InnoDB supports FULLTEXT, this trade-off is largely gone, though with a few operational differences: InnoDB maintains a cache table for recently added documents and only periodically writes changes into the actual inverted index, which can cause a short delay between INSERT and visibility in search results.

This delay can be controlled via innodb_ft_cache_size and a manual OPTIMIZE TABLE call, but is unproblematic for most applications since it is measured in a few seconds. For applications where newly inserted content must be immediately searchable, such as live chat search, this circumstance is relevant and should be considered in the system architecture.

8. Where MySQL full-text search hits its limits

MySQL full-text search is not a full replacement for dedicated search systems once certain requirements come into play. Fuzzy matching for typo tolerance, real faceting across multiple dimensions at once, complex synonym handling, or scaling across multiple servers with horizontal sharding capability are areas where specialized search systems like Elasticsearch or OpenSearch are clearly superior. Even with very large text volumes in the double-digit gigabyte range per table, the maintenance overhead of FULLTEXT indexes becomes noticeable.

Another practical edge case is the lack of native support for weighted multi-field searches with complex boost factors, commonly needed in e-commerce search, for example product name twice as important as description, triple weight on category match. Such requirements can only be replicated in MySQL through custom scoring formulas in the application layer, which, as complexity grows, suggests switching to a dedicated search system.

9. MySQL full-text search versus Elasticsearch

The overview below contrasts both approaches against practically relevant criteria.

For small to medium applications with manageable search needs and no requirements for fuzzy matching or faceting, MySQL full-text search is often the more pragmatic and lower maintenance solution. However, once requirements like typo tolerance, complex facets, or scaling beyond a single database server emerge, the advantages of a dedicated search system outweigh the additional operational complexity.

10. Summary

MySQL full-text search offers, through FULLTEXT indexes, a full-fledged, database-integrated full-text search sufficient for many applications without extra infrastructure. Natural language mode delivers relevance-based free-text search, boolean mode enables precise operators for advanced filtering. Stopwords and minimum word length need to be adjusted for non-English content, the ngram parser solves the problem of missing word boundaries in CJK languages.

The limits of MySQL full-text search lie in fuzzy matching, complex faceting, and horizontal scaling beyond a single server. Anyone without these requirements saves significant operational complexity with a DIY FULLTEXT implementation compared to a separate search system like Elasticsearch. The decision should always be made based on concrete requirements, not on the assumption that external search systems are inherently superior.

11. FAQ: MySQL full-text search