Searching Blog Content in MySQL: When FULLTEXT Beats LIKE

Your blog's search box works fine with 200 posts. At 20,000, every query takes seconds and the database CPU spikes. The culprit is almost always the same: WHERE content LIKE '%mysql%'. The leading wildcard means MySQL cannot use any index, so it reads every row, every time.

The fix is not necessarily Elasticsearch. MySQL has had FULLTEXT indexing built into InnoDB since version 5.6, and for straightforward keyword search over post titles and bodies, it is often the right amount of machinery. This post covers when to reach for it, how to set it up, and the two gotchas that bite people most often.

Why LIKE '%term%' cannot scale

A regular B-tree index is sorted by the column value, so it can answer "starts with" lookups efficiently. A pattern with a leading wildcard asks for the term anywhere in the text, which defeats that ordering. The optimizer has no choice but a full table scan, and cost grows linearly with your row count. LIKE also has no concept of relevance: a post mentioning "mysql" once ranks the same as one about nothing else.

A FULLTEXT index works differently. MySQL tokenizes the text into words, builds an inverted index mapping each word to the documents containing it, and scores matches by relevance. Lookup cost tracks the number of matching documents, not the table size.

Setting it up on a posts table

Assume MySQL 8.0 or later, InnoDB engine, and a table with a primary key (InnoDB FULLTEXT requires one). Run these in the MySQL client as a user with ALTER privileges on the table:

ALTER TABLE posts
  ADD FULLTEXT INDEX ft_posts_content (title, content);

On a large existing table this is a blocking or semi-online DDL operation depending on version, so schedule it. Then query with MATCH ... AGAINST:

SELECT id, title,
       MATCH(title, content) AGAINST ('innodb buffer pool' IN NATURAL LANGUAGE MODE) AS score
FROM posts
WHERE MATCH(title, content) AGAINST ('innodb buffer pool' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

Natural language mode ranks by relevance. Boolean mode gives explicit operators — +mysql -boolean requires "mysql" and excludes "boolean" — which is handy for power-user search syntax but returns unranked-ish results that can surprise people.

Verifying the index is actually used

Do not trust that the query got faster; check the plan. Run SHOW INDEX FROM posts; and confirm an entry with Index_type: FULLTEXT on your columns. Then:

EXPLAIN SELECT id FROM posts
WHERE MATCH(title, content) AGAINST ('innodb' IN NATURAL LANGUAGE MODE);

You want type of fulltext in the plan. If you see ALL, the optimizer fell back to a scan — a common cause is searching a column list that does not exactly match the index definition, or a table so small MySQL judges a scan cheaper.

The two gotchas: short words and stopwords

FULLTEXT ignores words below a minimum length — innodb_ft_min_token_size defaults to 3 — and skips a built-in stopword list of common words. For technical blogs this matters twice over. Two-character terms like "Go", "C", or "R" are silently unsearchable. And domain vocabulary can collide with stopwords or, conversely, terms like error codes may behave unexpectedly in boolean mode.

Check your settings with SHOW VARIABLES LIKE 'innodb_ft_min_token_size';. If you lower it, you must rebuild the FULLTEXT index (OPTIMIZE TABLE posts; or drop and re-add the index) for the change to apply to existing data. For CJK content, where words are not space-separated, look at the n-gram parser (WITH PARSER ngram) introduced in MySQL 5.7.

The trade-off, stated plainly

FULLTEXT is the right call when you need decent keyword search, you are already on MySQL, and your corpus fits comfortably in one database. It is the wrong call when you need fuzzy matching, typo tolerance, faceting, synonyms, or aggregations over search results — that is Elasticsearch/OpenSearch territory, at the cost of running a second system and a sync pipeline. FULLTEXT also offers limited ranking control, and index maintenance adds write overhead on every insert and update of the indexed columns.

What to do next

If your search is a LIKE query today: add a FULLTEXT index on a staging copy of the table, run the EXPLAIN check above, and compare latency on realistic queries. Measure with your actual data, because relevance quality is subjective and the only honest test is whether your users' real queries return sensible posts. For most blogs under a few million rows, you will get acceptable search without adding any infrastructure at all.