Full-text search and when not to use it

Full-text search in MySQL and when not to use it. LIKE ‘%keyword%’ on a big text column scans every row. FULLTEXT indexes exist for natural-language search on titles, bodies, and tags – but they are not Elasticsearch, and that is fine until it is not. Is one of those topics that pays off as soon as you use it on a real page.

Creating a FULLTEXT index

CREATE TABLE articles (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  published_at DATETIME,
  FULLTEXT INDEX ft_title_body (title, body)
);

InnoDB supports FULLTEXT from MySQL 5.6 onward. Minimum word length defaults to 3-4 characters depending on config – short tokens may be ignored.

MATCH … AGAINST

-- Natural language mode (default)
SELECT id, title,
       MATCH(title, body) AGAINST('mysql replication lag') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql replication lag')
ORDER BY score DESC
LIMIT 20;

-- Boolean mode: required/excluded terms
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('+mysql -oracle +replica*' IN BOOLEAN MODE);

Natural language mode ranks by relevance. Boolean mode gives you operators: + required, - excluded, * prefix wildcard.

When FULLTEXT is enough

• Site search on a blog or docs – thousands to low millions of rows
• Simple keyword matching with basic ranking
• You want results in SQL alongside filters (published date, category, author)
• Operational simplicity beats search quality – one system to backup and monitor

SELECT a.id, a.title, MATCH(a.title, a.body) AGAINST('index strategy') AS score
FROM articles a
INNER JOIN authors au ON au.id = a.author_id
WHERE a.published_at IS NOT NULL
  AND MATCH(a.title, a.body) AGAINST('index strategy')
  AND au.active = 1
ORDER BY score DESC;

When not to use it

Reach for OpenSearch, Elasticsearch, Meilisearch, or Typesense when you need:

• Typo tolerance and fuzzy matching at scale
• Faceted search – filters with counts updating as users narrow results
• Complex relevance tuning, synonyms, boosting by field or recency
• Very high query volume on huge corpora – search load off the OLTP database
• Highlighting snippets, autocomplete, and language-specific analysers

MySQL FULLTEXT also struggles with CJK text unless you configure ngram or MeCab parsers. English-centric blog search is the sweet spot.

LIKE vs FULLTEXT vs external search

-- Prefix search on indexed column: OK for autocomplete on sku
SELECT id, name FROM products WHERE sku LIKE 'WDG-%';

-- Leading wildcard: full scan, avoid on large tables
SELECT id, name FROM products WHERE name LIKE '%widget%';

-- FULLTEXT: indexed token search
SELECT id, name FROM products
WHERE MATCH(name, description) AGAINST('widget stand');

Keeping search indexes fresh

FULLTEXT indexes update on INSERT/UPDATE – write amplification on hot tables. External search usually means async indexing: app writes to MySQL, worker pushes changes to the search engine. Eventual consistency again.

If you outgrow FULLTEXT, denormalise a search document in a side table or export to a dedicated engine rather than bolting more logic onto MATCH.

The next tutorial covers JSON columns – flexible schema inside MySQL, with pitfalls that bite in production.