Cloud SQL vs RDS vs Azure Database: Managed MySQL and PostgreSQL Compared for 2026

Your e-commerce platform has grown to 8 million product listings. A customer types "wireless noise cancelling headphones" into the search box, and your LIKE '%wireless noise cancelling headphones%' query kicks off a full table scan — locking up your primary MySQL instance for 4.2 seconds and timing out for a third of users during peak traffic. You add ft_min_word_len tweaks and an index on the description column, but a B-tree index cannot rescue a pattern that starts with a wildcard. The fix is not more hardware or a cache layer — it is a fundamentally different indexing mechanism: MySQL's built-in FULLTEXT index combined with MATCH() AGAINST() queries, which can cut that 4.2-second scan to sub-100ms response times without leaving MySQL at all.

Background

MySQL has shipped FULLTEXT search support since version 3.23 for MyISAM and extended it to InnoDB in MySQL 5.6. Unlike a standard B-tree index — which stores sorted values and excels at equality lookups and range scans — a FULLTEXT index builds an inverted index: a mapping from each significant word to the list of rows and positions where that word appears. This makes it structurally similar to what a search engine like Elasticsearch builds under the hood, just without the distributed architecture.

When MySQL parses a FULLTEXT index, it runs the column text through a tokenizer (the built-in parser splits on whitespace and punctuation), removes stopwords, discards tokens shorter than innodb_ft_min_token_size, and writes the remaining tokens into three auxiliary InnoDB tables: the index table itself, a delete table, and a deleted cache. The result is that a MATCH() AGAINST() query can jump directly to matching rows rather than scanning every value.

There are three search modes, each with distinct behaviour: natural language mode (default, relevance ranked), boolean mode (operator-driven), and query expansion mode (two-pass, broadens results). Choosing the right mode for your use case is half the battle.

Creating FULLTEXT Indexes

You can add a FULLTEXT index at table creation time or alter an existing table. Both approaches produce identical results; the ALTER TABLE path is more common for production tables where data already exists.

At CREATE TABLE time

CREATE TABLE products (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title       VARCHAR(255)  NOT NULL,
  description TEXT          NOT NULL,
  brand       VARCHAR(100)  NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT INDEX ft_search (title, description, brand)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

A FULLTEXT index can span multiple columns. When you later query with MATCH(title, description, brand), you must list exactly the same columns in the same order as the index definition — MySQL will not use the index otherwise.

Adding to an existing table

-- Add a fulltext index on a single column
ALTER TABLE products ADD FULLTEXT INDEX ft_title (title);

-- Add a multi-column fulltext index
ALTER TABLE products ADD FULLTEXT INDEX ft_full (title, description);

-- Equivalent using CREATE INDEX syntax
CREATE FULLTEXT INDEX ft_brand ON products (brand);

Controlling minimum token size

-- Check current settings (InnoDB FTS)
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
-- Default: 3

-- Check MyISAM setting
SHOW VARIABLES LIKE 'ft_min_word_len';
-- Default: 4

-- Change in my.cnf (requires restart + OPTIMIZE TABLE)
-- [mysqld]
-- innodb_ft_min_token_size = 2

-- After restarting, rebuild the index
OPTIMIZE TABLE products;

MATCH AGAINST Modes

IN NATURAL LANGUAGE MODE

This is the default mode. MySQL calculates a relevance score for each row based on how frequently the search terms appear and how rare they are across the dataset (a TF-IDF-style calculation). Rows with a score of 0 are excluded from results.

-- Basic relevance search — returns rows ordered by relevance
SELECT
  id,
  title,
  MATCH(title, description) AGAINST('wireless headphones') AS relevance_score
FROM products
WHERE MATCH(title, description) AGAINST('wireless headphones')
ORDER BY relevance_score DESC
LIMIT 20;

Calling MATCH() AGAINST() twice — once in WHERE and once in SELECT — does not double the work. MySQL detects the duplicate and computes the score only once.

IN BOOLEAN MODE

Boolean mode gives you direct operator control over which terms must, must not, or optionally appear. It does not apply the 50% rule and does not sort by relevance by default (though you can still retrieve the score).

-- + means the term MUST be present
-- - means the term MUST NOT be present
SELECT id, title
FROM products
WHERE MATCH(title, description) AGAINST('+wireless -wired' IN BOOLEAN MODE);

-- * is a prefix wildcard (right-side only)
-- Matches: headphone, headphones, headband
SELECT id, title
FROM products
WHERE MATCH(title, description) AGAINST('head*' IN BOOLEAN MODE);

-- Phrase search with double quotes
SELECT id, title
FROM products
WHERE MATCH(title, description) AGAINST('"noise cancelling headphones"' IN BOOLEAN MODE);

-- ~ negates the relevance weight (demotes rows, does not exclude them)
SELECT id, title,
  MATCH(title, description) AGAINST('+headphones ~cheap' IN BOOLEAN MODE) AS score
FROM products
WHERE MATCH(title, description) AGAINST('+headphones ~cheap' IN BOOLEAN MODE)
ORDER BY score DESC;

-- Combining multiple operators
SELECT id, title
FROM products
WHERE MATCH(title, description)
  AGAINST('+wireless +"noise cancelling" -earbuds brand*' IN BOOLEAN MODE);

WITH QUERY EXPANSION

Query expansion runs a two-pass search: first it finds the top-ranked rows using natural language mode, then it extracts the most significant words from those rows and runs a second search using the expanded term list. This broadens recall at the cost of precision.

-- First pass finds rows matching 'database'
-- Second pass re-searches using words from those rows (e.g. 'MySQL', 'PostgreSQL', 'query')
SELECT id, title
FROM products
WHERE MATCH(title, description) AGAINST('database' WITH QUERY EXPANSION)
LIMIT 10;

Query expansion is best for short, ambiguous queries where users may not know the exact terminology. Avoid it for very small tables (the expansion set is noisy) or highly specific searches where precision matters more than recall.

InnoDB vs MyISAM Full-Text

MySQL 5.6 introduced FULLTEXT support in InnoDB, making MyISAM largely unnecessary for full-text workloads. The differences matter for production systems:

Performance Tuning

How InnoDB calculates relevance

InnoDB's relevance formula is a variant of BM25 (not strict TF-IDF, but similar in spirit). For each search term, the score for a row increases with term frequency in that row and decreases as the term becomes common across all rows. The final score you see in MATCH() AGAINST() is a floating-point value — higher is better, 0 means excluded.

-- Inspect relevance scores to understand ranking
SELECT
  id,
  title,
  ROUND(MATCH(title, description) AGAINST('wireless audio' IN NATURAL LANGUAGE MODE), 4) AS score
FROM products
WHERE MATCH(title, description) AGAINST('wireless audio' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 10;

-- Check the FTS auxiliary tables (useful for debugging)
SET GLOBAL innodb_ft_aux_table = 'mydb/products';
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 20;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 20;

Using EXPLAIN with FULLTEXT queries

EXPLAIN SELECT id, title
FROM products
WHERE MATCH(title, description) AGAINST('wireless headphones' IN BOOLEAN MODE)\G

-- Look for: type = fulltext, key = ft_full
-- This confirms the FULLTEXT index is being used
-- rows should be much lower than total table rows

Combining FULLTEXT with other indexes

-- Filter by category first (B-tree index), then rank by FTS relevance
SELECT p.id, p.title,
  MATCH(p.title, p.description) AGAINST('wireless headphones') AS score
FROM products p
WHERE p.category_id = 42
  AND MATCH(p.title, p.description) AGAINST('wireless headphones')
ORDER BY score DESC
LIMIT 20;

-- For the above query, add a composite index to help the category filter:
-- ALTER TABLE products ADD INDEX idx_category (category_id);
-- MySQL will often use the B-tree index to narrow rows, then apply FTS scoring

Managing the FTS cache and sync

-- innodb_ft_cache_size controls the per-index FTS cache (default 8MB)
-- innodb_ft_total_cache_size controls the global limit (default 640MB)
-- After bulk inserts, force a sync to flush the cache to disk:
OPTIMIZE TABLE products;

-- Check pending deletes (rows deleted but not yet purged from FTS index)
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;

-- Force FTS index rebuild entirely (also re-tokenizes all rows)
ALTER TABLE products DROP INDEX ft_full;
ALTER TABLE products ADD FULLTEXT INDEX ft_full (title, description);

MySQL FTS vs Elasticsearch

The right tool depends on your scale, query complexity, and operational appetite. Below is a practical comparison:

Key Takeaways

Working with JusDB on MySQL

Implementing MySQL FULLTEXT search correctly — tuning token sizes, managing stopword lists, combining FTS with B-tree indexes, deciding when to move to an external search engine — requires deep familiarity with InnoDB internals and production query patterns. JusDB's MySQL managed services cover the full lifecycle: schema design, index strategy, query tuning, replication, and scaling decisions.

If your application is hitting slow LIKE queries or you are evaluating whether MySQL FULLTEXT can replace a planned Elasticsearch cluster, our team can audit your current schema and query workload and give you a concrete recommendation.

• Explore JusDB MySQL Managed Services — query optimization, replication setup, and high-availability configurations.
• Talk to a MySQL Expert — get a free consultation on your full-text search architecture.

Related reading

• MySQL EXPLAIN Deep Dive: Reading Query Execution Plans — understand how MySQL chooses indexes so your FULLTEXT queries always hit the right path.
• OpenSearch vs Elasticsearch 2026: Which Search Engine Should You Choose? — when MySQL FULLTEXT is not enough and you need a dedicated search cluster.
• PostgreSQL Full-Text Search: tsvector, GIN Indexes, and ts_rank — if you are on PostgreSQL, this is the equivalent guide for native FTS with language dictionaries and GIN indexes.