✏️ Explanatory Question
Level: Hard — Tests whether you know the right tool for text search; LIKE does not scale and cannot rank relevance.
Scenario: Your app has a search box over a million-row articles table. You implemented it with WHERE body LIKE '%mysql%'. It is painfully slow, cannot rank results by relevance, and matches substrings inside other words. How do you build proper text search in MySQL?
'%text%') cannot use a normal index → full table scan every search. The fix — a FULLTEXT index: MySQL builds an inverted index of words, then you search it with MATCH(columns) AGAINST('terms'). It is fast (uses the index), word-aware, and returns a relevance score you can sort by.
CREATE TABLE articles (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
body TEXT,
FULLTEXT INDEX ft_title_body (title, body) -- the FULLTEXT index
) ENGINE=InnoDB; -- InnoDB supports FULLTEXT since MySQL 5.6
-- Add a FULLTEXT index to an existing table
ALTER TABLE articles ADD FULLTEXT INDEX ft_body (body);-- Find and RANK articles by relevance to "mysql performance"
SELECT
id, title,
MATCH(title, body) AGAINST('mysql performance') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('mysql performance')
ORDER BY relevance DESC
LIMIT 10;
-- Returns rows scored by how well they match, best first| Feature | LIKE '%x%' | FULLTEXT |
|---|---|---|
| Uses an index | No (full scan) | Yes (inverted index) |
| Relevance ranking | No | Yes (score) |
| Word boundaries | No (substring) | Yes (tokens) |
| Stopwords / min length | N/A | Yes |
| Scales to millions of rows | No | Yes |
Default gotchas to know: By default, InnoDB full-text ignores words shorter than innodb_ft_min_token_size (default 3 characters), and skips common stopwords (like "the", "is"). So searching "to be" may return nothing. These are configurable but bite people who do not expect them.
-- 1) Natural language mode (default) - relevance-ranked
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database tuning');
-- 2) Boolean mode - operators like + - * "..." (covered next question)
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+mysql -oracle' IN BOOLEAN MODE);
-- 3) Query expansion - broadens using top matches (blind relevance feedback)
SELECT * FROM articles
WHERE MATCH(title, body)
AGAINST('database' WITH QUERY EXPANSION);Interviewer follow-up: "When would you NOT use MySQL full-text search?" → For advanced search needs — typo tolerance (fuzzy), synonyms, faceting, multi-language stemming, or very high query volume — MySQL full-text is limited. Use a dedicated search engine like Elasticsearch/OpenSearch, keeping MySQL as the source of truth. MySQL FULLTEXT is great for simple-to-moderate in-app search, not a full search platform.