How to Perform Full-Text Search in MySQL Database

I remember the first time a client asked me to add a “search” box to their product catalog. My instinct was to reach for LIKE '%keyword%', and it worked fine — until the table hit a few hundred thousand rows and every search started taking seconds instead of milliseconds. That’s when I really dug into MySQL’s Full-Text Search capability, and it turned out to be one of the most underused features in the MySQL toolbox. In this article, I’ll share everything I’ve learned about setting it up, tuning it, and using it in real production systems.

What Is Full-Text Search?

Full-text search is a technique for searching natural-language text efficiently by using a specialized index — a FULLTEXT index — instead of scanning every row with pattern matching. Rather than treating a string as an opaque blob (like LIKE does), MySQL breaks the text into words, builds an inverted index of those words, and then can rank results by relevance.

This is fundamentally different from LIKE '%term%', which:

• Cannot use a B-tree index efficiently (leading wildcards force full scans).
• Has no concept of relevance ranking.
• Cannot understand word boundaries, stemming, or stop words.

MySQL Architecture Context: Where FULLTEXT Fits

graph TD
    A[Query: MATCH ... AGAINST] --> B[SQL Parser]
    B --> C[Optimizer]
    C --> D{FULLTEXT Index Available?}
    D -->|Yes| E[Inverted Index Lookup]
    D -->|No| F[Full Table Scan with LIKE]
    E --> G[Relevance Scoring]
    G --> H[Result Set Ordered by Score]

FULLTEXT indexes are supported by two storage engines: InnoDB (since MySQL 5.6) and MyISAM (the original implementation). I almost always use InnoDB today, since MyISAM lacks transactions and crash recovery, and there’s no good reason to sacrifice those just for full-text search anymore.

Creating a FULLTEXT Index

Let’s say I have a blog posts table:

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

If the table already exists, I add the index separately:

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

Note that a FULLTEXT index can span multiple columns, and MySQL treats them as a single combined text field for search purposes.

Basic Search Syntax: MATCH … AGAINST

The core syntax I use daily:

SELECT id, title, MATCH(title, body) AGAINST('database performance') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('database performance')
ORDER BY relevance DESC;

Sample output:

+----+----------------------------------+------------+
| id | title                            | relevance  |
+----+----------------------------------+------------+
|  4 | Improving Database Performance   | 8.213456   |
| 12 | MySQL Database Tuning Guide      | 5.771201   |
|  7 | Intro to Databases                | 1.982004   |
+----+----------------------------------+------------+

The relevance score is a floating-point value based on term frequency and inverse document frequency (TF-IDF-like scoring), not a fixed scale — it’s only meaningful relative to other rows in the same query.

Search Modes

MySQL supports three search modes, and knowing when to use each has saved me a lot of headaches.

1. Natural Language Mode (Default)

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('mysql indexing' IN NATURAL LANGUAGE MODE);

This treats the search string as a plain phrase, finding rows containing any of the significant words, ranked by relevance.

2. Boolean Mode

This is the mode I reach for most often in production because it gives fine-grained control:

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+mysql +index -oracle' IN BOOLEAN MODE);

Boolean operators I use regularly:

3. Query Expansion Mode

This mode performs the search twice — once to find the most relevant rows, then again including terms from those top matches, to broaden results:

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

I use this sparingly — it’s helpful for “did you mean” style broader recall, but it can pull in loosely related results if the top matches aren’t representative.

Stopwords and Minimum Word Length

MySQL, by default, ignores extremely common words (stopwords) like “the,” “and,” “is,” and words shorter than a minimum length (4 characters for InnoDB by default).

I check the current InnoDB stopword list like this:

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

To customize the minimum word length:

[mysqld]
innodb_ft_min_token_size = 3

To use a custom stopword table instead of the default:

CREATE TABLE my_stopwords (value VARCHAR(30)) ENGINE=InnoDB;
INSERT INTO my_stopwords VALUES ('the'), ('and'), ('for');

[mysqld]
innodb_ft_server_stopword_table = 'mydb/my_stopwords'

After changing these settings, I always rebuild the FULLTEXT index:

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

Internal Working: How the Inverted Index Works

Under the hood, InnoDB maintains an auxiliary FTS index table structure that maps each unique word to the list of document IDs (and positions) where it appears — this is the “inverted index.”

graph LR
    Word1[mysql] --> Doc1[doc_id: 4, pos: 2]
    Word1 --> Doc2[doc_id: 12, pos: 1]
    Word2[performance] --> Doc1
    Word2 --> Doc3[doc_id: 7, pos: 5]

New inserts don’t update this index instantly — InnoDB batches them in an in-memory cache (innodb_ft_cache_size) and periodically flushes to the on-disk index. This is why immediately-inserted rows may not appear in a full-text search until the cache flushes or a manual OPTIMIZE TABLE is run.

SET GLOBAL innodb_ft_cache_size = 8000000; -- 8MB

For applications needing near real-time search after insert, I’ve had to explicitly call:

OPTIMIZE TABLE articles;

though this rebuilds the whole table, so I avoid it too frequently on large tables in production — I schedule it during low-traffic windows.

Real-World Scenario: Product Search with Relevance Boosting

Here’s a pattern I used for an e-commerce catalog, boosting title matches over description matches:

SELECT id, name,
  (MATCH(name) AGAINST('wireless headphones' IN BOOLEAN MODE) * 2) +
  MATCH(description) AGAINST('wireless headphones' IN BOOLEAN MODE) AS score
FROM products
WHERE MATCH(name, description) AGAINST('wireless headphones' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 20;

This required two separate FULLTEXT indexes (name alone, description alone) since scoring is per-index-combination.

Full-Text Search vs. Dedicated Search Engines

I always set expectations with clients about what MySQL full-text search can and can’t do:

My rule of thumb: if search is a secondary feature and the dataset is under a few million rows, MySQL FULLTEXT is often good enough and saves significant operational complexity. If search is the product, I recommend a dedicated engine.

Performance and Optimization Tips

1. Index only what you search. Don’t add every text column to a FULLTEXT index — it increases index size and slows writes.
2. Use Boolean Mode for predictable performance. Natural language mode’s automatic relevance thresholds can sometimes exclude expected results.
3. Monitor cache size. innodb_ft_cache_size and innodb_ft_total_cache_size control memory usage for pending index updates.
4. Combine with normal WHERE clauses carefully. MySQL can use the FULLTEXT index for the MATCH condition and a secondary index for other filters, but complex combinations sometimes force a full scan — I check with EXPLAIN.

EXPLAIN SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('mysql' IN BOOLEAN MODE)
AND created_at > '2026-01-01';

5. Rebuild periodically for write-heavy tables. Deleted rows leave gaps in the index; OPTIMIZE TABLE reclaims that space.

Security Considerations

• Full-text search doesn’t inherently introduce SQL injection risk any more than other queries, but I always use parameterized queries when passing user search input into AGAINST().
• Boolean mode operators (+, -, ") are user-controllable — if I pass raw user input directly, users can craft unexpected boolean queries. I sanitize or escape special characters when I want literal-only searches.

-- Example of a parameterized query (in application code, not raw SQL)
SELECT * FROM articles WHERE MATCH(title, body) AGAINST(? IN BOOLEAN MODE);

Common Mistakes I See with Full-Text Search

A few recurring issues I’ve helped teams debug:

1. Expecting LIKE-style substring matching. Full-text search matches whole words (subject to stopwords and minimum length), not arbitrary substrings. Searching for “data” won’t necessarily match “database” unless you explicitly use the * wildcard in Boolean Mode (data*).
2. Not rebuilding the index after changing stopword or token-length settings. Configuration changes like innodb_ft_min_token_size only apply to future index builds — existing FULLTEXT indexes need to be dropped and recreated (or the table OPTIMIZEd) to take effect.
3. Passing raw, unescaped user input straight into Boolean Mode. Since +, -, ", and * are meaningful operators in Boolean Mode, a search box that passes user text unmodified can produce confusing or unintended query behavior if the user happens to type one of those characters.
4. Assuming relevance scores are comparable across different queries. The numeric score from MATCH...AGAINST only makes sense relative to other rows within the same query execution — I’ve seen dashboards incorrectly try to store and compare these scores over time as if they were an absolute metric.
5. Forgetting that FULLTEXT columns must match exactly. If you build an index on (title, body), you must call MATCH(title, body) with that exact column combination — MATCH(title) alone won’t use that same index.

Troubleshooting Common Issues

Interview Questions on MySQL Full-Text Search

1. What’s the difference between LIKE '%term%' and MATCH...AGAINST? LIKE cannot use an index for leading wildcards and offers no relevance ranking; MATCH...AGAINST uses an inverted FULLTEXT index with relevance scoring.
2. Which storage engines support FULLTEXT indexes? InnoDB (since 5.6) and MyISAM.
3. What are the three full-text search modes? Natural Language Mode, Boolean Mode, and Query Expansion Mode.
4. Why might a newly inserted row not appear in search results immediately? InnoDB batches full-text index updates in memory before flushing to disk; the row may not be indexed yet.
5. How do you exclude a word from Boolean Mode search results? Prefix it with a minus sign, e.g. AGAINST('+mysql -oracle' IN BOOLEAN MODE).

Frequently Asked Questions

Q: Can I use full-text search on JSON columns? A: Not directly — you’d typically extract the relevant text into a generated column or a separate TEXT column and index that.

Q: Does full-text search support multiple languages? A: MySQL’s built-in stopword and tokenization support is primarily optimized for space-delimited languages like English. For CJK languages, MySQL provides an n-gram parser (ngram full-text parser) as an alternative.

Q: Is there a minimum table size before FULLTEXT makes sense? A: Not strictly, but I usually only bother once LIKE queries start showing measurable latency — often in the tens of thousands of rows range, depending on hardware.

Q: Can I combine full-text search with ORDER BY on another column? A: Yes, but if you order by something other than relevance, MySQL may not use the FULLTEXT index as efficiently — test with EXPLAIN.

Summary and Key Takeaways

MySQL’s full-text search feature gives you a surprisingly capable search tool without leaving the database. I’ve used it successfully for blogs, product catalogs, and support ticket systems.

Key takeaways:

• Use InnoDB FULLTEXT indexes over MyISAM for transactional safety.
• Boolean Mode gives the most predictable, production-ready control.
• Watch out for stopwords, minimum token length, and the FTS cache delay.
• For search-heavy applications at scale, consider pairing MySQL with a dedicated search engine.
• Always verify query plans with EXPLAIN to confirm the FULLTEXT index is actually being used.

References

• MySQL Reference Manual: Full-Text Search Functions
• MySQL Reference Manual: InnoDB Full-Text Indexes
• MySQL Reference Manual: Full-Text Stopwords