Full-text search

Searching text with LIKE '%word%' works on small tables, but it scans every row, can't rank results by relevance and can't handle "this word but not that one". A FULLTEXT index splits text into words and indexes each one, so word searches stay fast on millions of rows and results come back ranked by relevance.

Creating a FULLTEXT index#

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

INSERT INTO articles (title, body) VALUES
    ('Getting started with MySQL', 'Install MySQL, create a database and run your first queries.'),
    ('MySQL replication explained', 'Replication copies changes from a source server to replicas using binary logs.'),
    ('Tuning the InnoDB buffer pool', 'The buffer pool caches data and indexes in memory. Size it for your working set.'),
    ('PostgreSQL vs MySQL', 'Both databases are popular. PostgreSQL has rich types while MySQL is famous for replication and speed.'),
    ('Backups with mysqldump', 'Logical backups are portable. Combine dumps with binary logs for point-in-time recovery.'),
    ('Indexing strategies', 'Composite indexes follow the leftmost prefix rule. Covering indexes avoid table lookups.');

You can also add one later: ALTER TABLE articles ADD FULLTEXT INDEX ft_body (body);. FULLTEXT works on CHAR, VARCHAR and TEXT columns, in InnoDB and MyISAM.

Natural-language search#

SELECT id, title,
       ROUND(MATCH(title, body) AGAINST ('replication'), 4) AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('replication')
ORDER BY score DESC;

+----+-----------------------------+--------+
| id | title                       | score  |
+----+-----------------------------+--------+
|  2 | MySQL replication explained | 0.4553 |
|  4 | PostgreSQL vs MySQL         | 0.2276 |
+----+-----------------------------+--------+

• MATCH(columns) must list exactly the columns of a FULLTEXT index, in any order.
• AGAINST('words') with no modifier is natural-language mode. Rows that contain any of the words match, and rarer words and more occurrences give higher scores.
• In the WHERE clause, results are automatically sorted by relevance. Selecting the MATCH expression shows the score, and MySQL computes it only once.

A multi-word query matches rows with any of the words:

SELECT title FROM articles
WHERE MATCH(title, body) AGAINST ('buffer backups');

+-------------------------------+
| title                         |
+-------------------------------+
| Tuning the InnoDB buffer pool |
| Backups with mysqldump        |
+-------------------------------+

Boolean mode: precise control#

IN BOOLEAN MODE adds operators:

-- Must mention mysql, must not mention postgresql
SELECT title FROM articles
WHERE MATCH(title, body) AGAINST ('+mysql -postgresql' IN BOOLEAN MODE);

+-----------------------------+
| title                       |
+-----------------------------+
| Getting started with MySQL  |
| MySQL replication explained |
+-----------------------------+

-- Prefix search and phrase search
SELECT title FROM articles WHERE MATCH(title, body) AGAINST ('index*' IN BOOLEAN MODE);
SELECT title FROM articles WHERE MATCH(title, body) AGAINST ('"binary logs"' IN BOOLEAN MODE);

+-------------------------------+
| title                         |
+-------------------------------+
| Indexing strategies           |
| Tuning the InnoDB buffer pool |
+-------------------------------+
+-----------------------------+
| title                       |
+-----------------------------+
| MySQL replication explained |
| Backups with mysqldump      |
+-----------------------------+

Boolean mode is what you usually want behind a search box. Translate user input into +word* terms so every word is required and partial words still match.

Query expansion#

WITH QUERY EXPANSION runs the search twice: first normally, then again adding words from the top results. It can find related documents that don't contain the original word, but it often adds noise. Use it for short queries, and test it.

SELECT title FROM articles
WHERE MATCH(title, body) AGAINST ('dumps' WITH QUERY EXPANSION);

What gets indexed: tokens, stopwords and length#

The full-text parser splits text on spaces and punctuation. Two rules surprise people:

1. Minimum word length. InnoDB ignores words shorter than innodb_ft_min_token_size (default 3), so go, ai and db aren't indexed. MyISAM's default (ft_min_word_len) is 4.
2. Stopwords. Very common words (the, and, it, about, …) are skipped. See the default list with SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;.

SELECT COUNT(*) FROM articles WHERE MATCH(title, body) AGAINST ('the');

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

Changing innodb_ft_min_token_size requires a server restart and rebuilding the FULLTEXT indexes. You can also supply a custom stopword table (innodb_ft_server_stopword_table). Searches are case-insensitive with the usual _ci collations.

CJK and other languages

The default parser assumes spaces between words. For Chinese, Japanese and Korean, use the built-in ngram parser:

CREATE TABLE notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    body TEXT,
    FULLTEXT INDEX ft_body (body) WITH PARSER ngram
);

Combining full-text with normal filters#

MATCH combines freely with other conditions, joins and pagination:

SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('+mysql*' IN BOOLEAN MODE)
  AND id > 1
ORDER BY MATCH(title, body) AGAINST ('+mysql*' IN BOOLEAN MODE) DESC, id
LIMIT 3;

+----+-----------------------------+
| id | title                       |
+----+-----------------------------+
|  4 | PostgreSQL vs MySQL         |
|  2 | MySQL replication explained |
|  5 | Backups with mysqldump      |
+----+-----------------------------+

Limitations, and when to use a search engine#

MySQL full-text search is great for "search the blog / help centre / product names" features without extra infrastructure. It doesn't offer:

• typo tolerance or fuzzy matching
• stemming (run vs running) or synonyms, beyond what prefix search gives you
• facets, highlighting, or advanced relevance tuning

When you need those, or are searching across many millions of large documents, use a dedicated engine such as OpenSearch/Elasticsearch, Meilisearch or Typesense, fed from MySQL. Even then, MySQL full-text search is often the right first version.

Common mistakes#

• MATCH(title) when the index is on (title, body) fails with ERROR 1191: Can't find FULLTEXT index matching the column list. Create an index for each column combination you search.
• Expecting results for short words or stopwords.
• Bulk-loading large tables with the FULLTEXT index in place. Loading first and then adding the index is much faster.
• Deleted rows linger in the index until OPTIMIZE TABLE (with innodb_optimize_fulltext_only=ON) cleans them up, which matters for tables with heavy churn.

What's next#

Finally, you'll learn how MySQL scales and stays available: replication and the core performance settings every MySQL admin should know.