MySQL – Full-Text Search

LIKE '%chicken%' is how most search boxes start, and it has two problems: it cannot use an index, so it reads every row, and it has no idea which result is the best one. A FULLTEXT index fixes both — it tokenises the text into words, indexes those, and ranks matches by relevance.

Creating the index

ALTER TABLE product ADD FULLTEXT INDEX ft_product_search (name, description);

The index covers the listed columns as a set. That matters: a MATCH must name exactly the same columns in the same order, or MySQL reports "Can't find FULLTEXT index matching the column list". Searching name alone needs its own index.

InnoDB has supported FULLTEXT since MySQL 5.6 — the old advice that it is MyISAM-only is long out of date.

Natural language mode

SELECT name, ROUND(MATCH(name, description) AGAINST ('chicken'), 4) AS score
FROM   product
WHERE  MATCH(name, description) AGAINST ('chicken')
ORDER  BY score DESC, id;

+-----------------------+--------+
| name                  | score  |
+-----------------------+--------+
| BBQ Chicken Pizza     | 1.4284 |
| Buffalo Chicken Pizza | 1.4284 |
+-----------------------+--------+

MATCH(cols) AGAINST (terms) is the whole syntax. In the WHERE clause it filters; in the select list the same expression yields the relevance score, a positive float where bigger is better. The number has no absolute meaning — it is only comparable within one query — and MySQL is smart enough to compute it once when it appears twice.

Natural language mode is forgiving: no operators, word order ignored, rows with more of the terms scoring higher. It is the right default for a search box.

Boolean mode

SELECT name FROM product
WHERE  MATCH(name, description) AGAINST ('+chicken -buffalo' IN BOOLEAN MODE)
ORDER  BY id;

+-------------------+
| name              |
+-------------------+
| BBQ Chicken Pizza |
+-------------------+

Boolean mode adds operators:

SELECT name FROM product
WHERE  MATCH(name, description) AGAINST ('pepper*' IN BOOLEAN MODE)
ORDER  BY id;

+---------------------+
| name                |
+---------------------+
| Pepperoni Pizza     |
| Supreme Pizza       |
| Meat Lovers Pizza   |
| Veggie Lovers Pizza |
+---------------------+

One prefix, matching both pepperoni and peppers. Note that boolean mode does not sort by relevance automatically, so add your own ORDER BY.

Why short searches return nothing

This is the behaviour that makes people give up on full-text search, and it is two settings.

Minimum token length. InnoDB's innodb_ft_min_token_size defaults to 3, so anything shorter is never indexed:

SELECT COUNT(*) AS hits_ou FROM product WHERE MATCH(name, description) AGAINST ('ou');

+---------+
| hits_ou |
+---------+
|       0 |
+---------+

Stopwords. InnoDB ships a list of 36 very common English words that are never indexed — a, the, is, of, and so on:

SELECT COUNT(*) AS hits_the FROM product WHERE MATCH(name, description) AGAINST ('the');

+----------+
| hits_the |
+----------+
|        0 |
+----------+

You can inspect the exact list — it is a table:

SELECT COUNT(*) AS stopwords FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

Both are changeable, and changing either requires rebuilding the index (OPTIMIZE TABLE with innodb_optimize_fulltext_only, or drop and recreate). Note also that MyISAM has its own separate setting, ft_min_word_len, defaulting to 4 — so advice you find online may be about the wrong engine.

What it costs

A FULLTEXT index is maintained on every insert and update to the covered columns, so writes get slower and the index takes space. On a table that is written far more often than it is searched, that trade may not be worth it.

The index is also word-based, which is exactly why it is fast and exactly what it cannot do: no substring matching inside a word (beyond a prefix), no fuzzy or typo-tolerant matching, no stemming — pizza and pizzas are different tokens — and no synonyms. It also has one language's idea of what a word is; ngram exists for Chinese, Japanese and Korean.

Where it stops being enough

MySQL full-text search is a good fit for searching a product catalogue, a help centre, or a handful of text columns in an application that already uses MySQL. It saves you an entire extra system.

Reach for a search engine — Elasticsearch or OpenSearch — when you need typo tolerance, stemming and synonyms, faceting and aggregations over results, ranking you can tune, or search across sources that are not all in this database. The demo application makes exactly that call: it has an optional Elasticsearch profile for product search, and runs on MySQL alone without it.

The cost of that decision is a second copy of the data that has to be kept in step, and it is a real cost. Do not pay it until the list above contains something you actually need.

What to remember

• MATCH must name exactly the columns of the FULLTEXT index.
• Natural language mode ranks; boolean mode takes operators and does not sort for you.
• Words shorter than innodb_ft_min_token_size (3) and the 36 stopwords are never indexed.
• Changing either setting means rebuilding the index.
• No stemming, no fuzziness. That is when a search engine starts earning its cost.