Full-text Search

Every shop website has a search box. A customer types "speaker not charging" and expects the most useful help articles first, in a fraction of a second. You could try LIKE '%speaker%', but it is slow on big tables, matches pieces of words, and has no idea which result is most relevant.

MySQL's answer is full-text search: a special index of the words in your text columns, and the MATCH ... AGAINST syntax to search it. In this lesson you will see where LIKE falls short, create a FULLTEXT index, search in natural language and boolean mode, and learn the rules about short words and common words that surprise everyone the first time.

The problem with LIKE

The practice database has no long text, so this lesson uses a small table of help articles. The first example runs in the browser and searches it with LIKE:

CREATE TABLE help_articles (
  article_id INTEGER PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  body TEXT NOT NULL
);

INSERT INTO help_articles (article_id, title, body) VALUES
  (1, 'Pairing a Bluetooth speaker', 'Switch the speaker on, hold the pairing button for three seconds and choose it on your phone.'),
  (2, 'Speaker will not charge', 'Use the cable from the box. If the charging light stays off, the speaker may need a repair.'),
  (3, 'Choosing a mechanical keyboard', 'Blue switches click loudly and red switches are quiet. Pair a wireless keyboard using its USB receiver.'),
  (4, 'Returns and refunds', 'You can return most products within 7 days. Refunds reach your bank in 5 to 7 working days.'),
  (5, 'Pen drive or external SSD?', 'A pen drive is cheap and small. An external SSD is much faster for videos and backups.'),
  (6, 'Keeping your laptop cool', 'A laptop stand lets air flow under the laptop, so the fan does not have to work so hard.'),
  (7, 'Webcam picture is blurry', 'Clean the lens with a soft cloth and choose 1080p in the camera app for the HD webcam.');

SELECT article_id, title
FROM help_articles
WHERE title LIKE '%pair%' OR body LIKE '%pair%'
ORDER BY article_id;

article_id | title
-----------+-------------------------------
1          | Pairing a Bluetooth speaker
2          | Speaker will not charge
3          | Choosing a mechanical keyboard
(3 rows)

The customer wanted help with pairing, but article 2 appears because "repair" contains "pair". LIKE '%...%' has three weaknesses:

• It matches inside words, so "pair" finds "repair", and "cam" finds "camera" and "webcam".
• It cannot use an index. A pattern that starts with % forces MySQL to read every row (see Indexes & EXPLAIN). Fine for 7 rows, painful for 7 lakh.
• It has no ranking. Every match is equally good, so the best answer may be on page three.

Creating a FULLTEXT index

A full-text index is an inverted index: for every word, it stores the rows that contain it, a little like the index at the back of a book. You can add one to CHAR, VARCHAR and TEXT columns in InnoDB or MyISAM tables. The rest of this lesson needs a real MySQL server, because the browser's SQLite does not support it:

ALTER TABLE help_articles
  ADD FULLTEXT INDEX ft_title_body (title, body);

You can also write FULLTEXT (title, body) inside CREATE TABLE. When you load a big table, insert the data first and add the index afterwards; building it once is much faster than updating it row by row.

An index covers a fixed list of columns, and MATCH() must name exactly those columns. With the index above, MATCH(title, body) works, but MATCH(title) fails with ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list. If you also want to search titles alone, add a second index on (title).

Natural language search

The default mode treats the search text as ordinary words. It finds rows containing any of the words and gives each row a relevance score: higher when the word appears more often in the row, and higher for rare words than for common ones.

SELECT article_id, title
FROM help_articles
WHERE MATCH(title, body) AGAINST('pair');

article_id | title
-----------+-------------------------------
3          | Choosing a mechanical keyboard
(1 row)

Only article 3 contains the word "pair". "Repair" and "pairing" are different words, so they are not matched. That is more precise than LIKE, though it also means "pairing" is missed; you will fix that with boolean mode in a moment.

MATCH() can also appear in the column list, where it returns the score. Here a two-word search is sorted by relevance:

SELECT article_id, title,
       ROUND(MATCH(title, body) AGAINST('laptop speaker'), 2) AS score
FROM help_articles
WHERE MATCH(title, body) AGAINST('laptop speaker')
ORDER BY score DESC, article_id;

article_id | title                       | score
-----------+-----------------------------+------
6          | Keeping your laptop cool    | 2.14
1          | Pairing a Bluetooth speaker | 0.59
2          | Speaker will not charge     | 0.59
(3 rows)

"Laptop" appears three times in article 6 and in no other article, so that row scores highest. "Speaker" appears in two articles, so it is less rare and counts for less. When MATCH() is in the WHERE clause and there is no ORDER BY, MySQL usually returns rows best-first anyway, but writing ORDER BY makes the order certain. The exact numbers do not matter; only their order does.

Boolean mode

IN BOOLEAN MODE lets the person searching give orders with a few symbols:

SELECT article_id, title
FROM help_articles
WHERE MATCH(title, body) AGAINST('pair*' IN BOOLEAN MODE)
ORDER BY article_id;

article_id | title
-----------+-------------------------------
1          | Pairing a Bluetooth speaker
3          | Choosing a mechanical keyboard
(2 rows)

pair* finds "pair" and "pairing" but still not "repair", because the wildcard works only at the end of a word. In the same way, '+speaker -repair' returns only article 1, and '"pen drive"' returns only article 5. Boolean mode does not sort by relevance on its own, so add ORDER BY MATCH(...) AGAINST(... IN BOOLEAN MODE) DESC when the order matters.

A third mode, WITH QUERY EXPANSION, runs the search twice: it takes words from the best matches of the first search and searches again with them. It can find related articles that never use the exact word, but it also adds noise, so use it with care.

Short words and stopwords

Two rules decide which words are indexed at all, and both cause "why does my search return nothing?" moments.

Minimum word length. InnoDB ignores words shorter than innodb_ft_min_token_size, which is 3 by default (MyISAM uses ft_min_word_len, default 4). "SSD" and "fan" are indexed, but "HD" is not, so AGAINST('HD') returns no rows even though article 7 contains it. You can lower the setting, but it is read only at server start, and you must rebuild every full-text index afterwards, for example by dropping and re-adding it.

Stopwords. Very common words such as "the", "is", "for" and "with" are on a built-in stopword list and are never indexed, because they appear everywhere and tell you nothing. AGAINST('the') finds nothing. InnoDB lets you supply your own list through the innodb_ft_user_stopword_table or innodb_ft_server_stopword_table settings.

When full-text search is not enough

MySQL's full-text search is a good fit for help pages, blog posts and product descriptions on small and medium sites. It does not correct spelling ("speeker"), understand that "earphones" and "headphones" are related, or handle Chinese, Japanese and Korean text with the default parser; for those three languages MySQL includes an ngram parser (FULLTEXT (body) WITH PARSER ngram). Large shops often copy their product data into a dedicated search engine such as Elasticsearch, OpenSearch or Meilisearch. SQLite has its own full-text feature, FTS5, which uses a different syntax from MySQL.

Common mistakes

• MATCH() columns that do not match the index. The list in MATCH() must be exactly the column list of one FULLTEXT index.
• Searching for short or common words. "HD", "TV" and "the" are not in the index with default settings.
• Expecting boolean results in relevance order. Add an explicit ORDER BY.
• Building the search string from user input. Pass it as a prepared statement parameter, AGAINST(?), like any other value.

Key points

• LIKE '%word%' matches inside words, cannot use an index and does not rank results.
• A FULLTEXT index lists the words in CHAR, VARCHAR and TEXT columns; search it with MATCH(columns) AGAINST('text').
• The MATCH() column list must equal a FULLTEXT index's column list.
• Natural language mode returns a relevance score; boolean mode supports +, -, *, quotes and more.
• InnoDB skips words shorter than 3 characters and stopwords such as "the".
• For spelling tolerance and very large catalogues, use a dedicated search engine.

SELECT article_id, title,
       ROUND(MATCH(title, body) AGAINST('keyboard switches'), 2) AS score
FROM help_articles
WHERE MATCH(title, body) AGAINST('keyboard switches')
ORDER BY score DESC;

SELECT article_id, title
FROM help_articles
WHERE MATCH(title, body) AGAINST('+refund* -bank' IN BOOLEAN MODE);

SELECT article_id, title
FROM help_articles
WHERE MATCH(title, body) AGAINST('"external SSD"' IN BOOLEAN MODE);