Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 75 ยท JSON and Modern Features

Full-Text Search

Search text in MySQL with FULLTEXT indexes and MATCH AGAINST: natural-language and boolean modes, relevance, stopwords, ngram for CJK, and when to use a search engine instead.

WHERE body LIKE '%mysql%' looks like search, but it is a slow substring test. It cannot use an ordinary index when the pattern starts with %, it has no idea which results are more relevant, and it happily matches mysql inside mysqldump or cat inside category. MySQL has a built-in answer for real text search: the FULLTEXT index and the MATCH ... AGAINST operator. They split text into words, keep an inverted index from words to rows, and rank results by relevance.

This lesson shows how to create and query a full-text index, explains the two main search modes, and is honest about where MySQL's built-in search stops and a dedicated engine such as Meilisearch or Elasticsearch takes over. Full-text search is a MySQL server feature, and the browser's SQLite build here has no full-text module, so the MySQL syntax is read-only. Live examples build a tiny inverted index by hand, which is a good way to see what the server does for you.

How full-text search works

Tokenize Split text into words and drop stopwords.
Inverted index Each word points to the rows that contain it.
Rank Rare words in short texts score higher.

LIKE scans text. A full-text index looks words up.

1 / 2

Creating a FULLTEXT index

A FULLTEXT index works on CHAR, VARCHAR and TEXT columns, and an index can cover several of them. InnoDB supports it (as does MyISAM, which is why you will see older tutorials using it).

SQL
CREATE TABLE articles (
  id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  body  TEXT NOT NULL,
  FULLTEXT KEY ft_articles (title, body)
) ENGINE = InnoDB;

-- or on an existing table
ALTER TABLE articles ADD FULLTEXT KEY ft_title (title);
-- CREATE FULLTEXT INDEX ft_title ON articles (title);

Adding the first FULLTEXT index to a big table can rebuild the table, so plan it like any other ALTER TABLE. Rows you insert are searchable once their transaction commits.

Natural-language mode

The default mode takes a phrase of ordinary words. MySQL looks for rows that contain any of them and sorts the best matches first:

SQL
SELECT id, title,
       MATCH(title, body) AGAINST('database index performance') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('database index performance' IN NATURAL LANGUAGE MODE)
LIMIT 10;

Rules to remember:

  • The column list inside MATCH(...) must be exactly the column list of one FULLTEXT index. MATCH(title) will not use an index on (title, body) and fails with an error instead.
  • MATCH ... AGAINST returns a floating-point relevance score: higher means a better match, and 0 means no match. The score depends on how often the words occur in the row, how rare they are across the table and how long the text is. Scores are only comparable within one query.
  • When MATCH is in the WHERE clause and you give no ORDER BY, rows come back sorted by relevance. If you add an ORDER BY on something else, the ranking is lost, so that is a choice to make deliberately.
  • Words are matched exactly: there is no stemming, so indexes does not find index, and no typo tolerance, so databse finds nothing.
  • Search values are plain words. Punctuation is treated as a word separator.

WITH QUERY EXPANSION runs the search twice, adding words from the best hits to the second pass. It broadens results but often adds noise, so it is rarely the right default.

Boolean mode

When the user (or you) need control, switch to boolean mode. Operators sit directly in front of or behind a word:

Operator Meaning
+word The word must be in the row.
-word The word must not be in the row.
word Optional: it helps the score if present.
word* Prefix match: data* finds data, database, datasets.
"exact phrase" The words must appear next to each other in that order.
>word and <word Raise or lower the word's contribution to the score.
( ) Group terms into sub-expressions.
~word Lower the score if present, without excluding the row.
SQL
SELECT id, title
FROM articles
WHERE MATCH(title, body)
      AGAINST('+mysql +index* -postgresql "query plan"' IN BOOLEAN MODE);

This finds rows that contain mysql, contain a word starting with index, do not contain postgresql, and optionally contain the phrase query plan (which only improves ranking). Boolean mode does not sort by relevance unless you add an ORDER BY on the score. A query that consists only of - terms returns nothing, because there is no positive match to subtract from. If you pass user input straight into boolean mode, they can type operators, so strip or escape characters such as + - < > ( ) ~ * " if you want plain search.

Tokens, stopwords and ngram

The index only stores words that survive tokenizing:

  • Minimum word length. InnoDB ignores words shorter than innodb_ft_min_token_size, 3 characters by default, so a search for go or C finds nothing. The setting is a startup option, and after changing it you must rebuild the index (OPTIMIZE TABLE or drop and recreate it).
  • Stopwords. Very common words (the, and, ...) from a built-in list are not indexed, and a search for only a stopword returns nothing. You can supply your own list through a table (innodb_ft_user_stopword_table) or switch the feature off (innodb_ft_enable_stopword).
  • Word boundaries. Words are separated by spaces and punctuation. That works well for English and most European languages and fails for Chinese, Japanese and Korean, which do not put spaces between words.

For those languages, MySQL ships the ngram parser. It indexes overlapping character sequences of fixed length instead of words:

SQL
CREATE TABLE posts_zh (
  id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  body TEXT NOT NULL,
  FULLTEXT KEY ft_body (body) WITH PARSER ngram
);

The sequence length is the startup option ngram_token_size (default 2). A search term shorter than the token size does not match anything useful, so choose the size to suit your language and rebuild the index when you change it.

Maintenance notes: InnoDB keeps new entries in a cache and merges them into its index tables in the background. Deleted rows leave stale entries until the index is optimized. For tables with heavy churn, schedule OPTIMIZE TABLE during quiet hours, with innodb_optimize_fulltext_only set so that only the full-text index is processed.

See the idea: LIKE versus tokens

The substring problem is easy to reproduce. LIKE '%cat%' matches every row that contains the three letters in a row, anywhere. A word-based search would only match the word:

SQL Why LIKE is not search

Press Run (or Ctrl + Enter) to see the result here.

The first query returns four rows, including category and concatenate, which are not about cats. The second returns only the two rows that contain the word cat. A real full-text index does this without scanning, and it also handles punctuation, case and ranking.

Build a tiny inverted index

This is a stand-in, not MySQL's implementation, but it follows the same plan: split each text into words, drop stopwords, and store one (word, document) entry per pair. After that, search is a lookup in the index and a count of matched terms is a crude relevance score. The LIKE 'quer%' query mimics the boolean quer* operator, and the IN and NOT IN pair mimics +indexes -postgresql:

SQL A hand-made inverted index

Press Run (or Ctrl + Enter) to see the result here.

The index ends up with 17 entries for 13 distinct words (the stopwords in, and, at and up never enter it). The search for mysql search ranks document 2 first, since it contains both words, and documents 1 and 3 after it with one each. The boolean-style query finds only document 1: document 4 also has indexes but is excluded for containing postgresql. The prefix query finds documents 1 and 4, since both contain queries.

Limits and alternatives

MySQL full-text search is a good fit for a modest corpus (a help center, a product catalog, a blog) where the data already lives in MySQL and you want zero extra infrastructure. It has real limits:

  • No stemming, synonyms, typo tolerance or "did you mean".
  • No highlighting or snippets (you must build them in the application), no facets, and few ways to tune ranking beyond boolean weights.
  • Weak language support beyond whitespace tokenizing and ngram.
  • Search load competes with your transactional workload on the same server, and very large indexes get slow to maintain.

When search quality becomes a product feature, a dedicated engine such as Meilisearch, Typesense or Elasticsearch/OpenSearch is the better tool. The cost is a second system to run and keep in sync: you index from MySQL after each change (through a queue or change-data-capture), and you accept a short delay before new rows appear. A common path is to start with FULLTEXT, measure, and move when the limits start to hurt.

What ran live here

The LIKE comparison and the hand-built inverted index ran in SQLite. FULLTEXT indexes, MATCH ... AGAINST, the boolean operators, stopword and token-size settings and the ngram parser are MySQL server features and are shown as read-only code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which statement about MATCH(title, body) AGAINST(...) is true for InnoDB?

    The column list in MATCH must be the column list of one FULLTEXT index, otherwise MySQL raises an error.
  2. 2. In boolean mode, what does the search string '+mysql -oracle' mean?

    A leading + makes a word required and a leading - excludes rows that contain it.
  3. 3. Why does a search for the two-letter word go find nothing with default InnoDB settings?

    InnoDB indexes only words of at least 3 characters by default. The size is a startup option and needs an index rebuild after a change.
  4. 4. Which parser should you choose for a table of Chinese or Japanese text?

    Those languages have no spaces between words, so the ngram parser indexes overlapping character sequences instead of words.
  5. 5. Which need most clearly suggests moving from MySQL FULLTEXT to a dedicated search engine?

    Typo tolerance, synonyms, highlighting and ranking control are strengths of Meilisearch, Typesense and Elasticsearch, not of MySQL's built-in search.

Summary

  • A FULLTEXT index stores an inverted index of words. MATCH(cols) AGAINST('text') uses it, and the column list must equal an index's column list.
  • Natural-language mode takes plain words and returns a relevance score, sorted best-first when MATCH is in WHERE without ORDER BY.
  • Boolean mode adds +, -, *, quoted phrases and grouping. Escape user input before using it there.
  • Short words, stopwords and languages without spaces need attention: innodb_ft_min_token_size, the stopword list and the ngram parser.
  • There is no stemming or typo tolerance. When search is a product feature, use a dedicated engine.

Next: Spatial Data and Geometry.