MySQL Full-Text Search in 2026: Features, Limitations, and Comparison with Elasticsearch

Introduction: When Built-In Search Is Enough — and When It Isn't

In 2026, the question of whether to use MySQL Full-Text Search or bring in Elasticsearch remains relevant for hundreds of thousands of projects. Many teams reach for Elasticsearch out of habit, without first checking whether MySQL's native search can handle their actual workload. Others, on the contrary, hit the ceiling of FULLTEXT indexes when a catalog grows to millions of records.

A simple rule of thumb: if you have up to 5–10 million rows, search queries are no more complex than boolean conditions, and relevance requirements are moderate — MySQL Full-Text Search will get the job done without any additional infrastructure. Once you need faceted search, synonyms, multilingual stemming, or streaming indexation, it's time to look at Elasticsearch.

Full-Text Index Types and Search Modes in MySQL

FULLTEXT Index

MySQL supports FULLTEXT indexes only for InnoDB and MyISAM tables. You can create an index at table creation time or add it later:

-- At table creation
CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  FULLTEXT idx_fts (title, body)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Adding to an existing table
ALTER TABLE articles ADD FULLTEXT INDEX idx_fts (title, body);

-- Or via CREATE INDEX
CREATE FULLTEXT INDEX idx_fts ON articles (title, body);

MATCH...AGAINST and Search Modes

The MATCH(col1, col2) AGAINST('query' IN MODE) operator is the only way to use a FULLTEXT index. MySQL supports three modes:

• IN NATURAL LANGUAGE MODE — the default mode. MySQL tokenizes the query, removes stop words, and ranks results using a TF-IDF-like algorithm.
• IN BOOLEAN MODE — boolean search with operators +, -, *, "", >, <. Allows complex queries without default relevance ranking.
• WITH QUERY EXPANSION — a two-pass search: first finds relevant documents, then expands the query with words from those documents. Improves recall but may reduce precision.

-- NATURAL LANGUAGE MODE
SELECT id, title,
       MATCH(title, body) AGAINST('full-text search MySQL') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('full-text search MySQL')
ORDER BY score DESC
LIMIT 20;

-- BOOLEAN MODE: required word, exclusion, phrase
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -Oracle "full-text search"' IN BOOLEAN MODE);

-- WITH QUERY EXPANSION
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('indexing' WITH QUERY EXPANSION)
LIMIT 10;

Configuring and Optimizing FTS Indexes

Minimum Word Length

By default, MySQL indexes words of 3 characters or more (innodb_ft_min_token_size = 3 for InnoDB, ft_min_word_len = 4 for MyISAM). For certain content types this can be a problem: short but meaningful words (like "go", "id", "SQL") get excluded from the index.

# my.cnf / my.ini
[mysqld]
# For InnoDB
innodb_ft_min_token_size = 2

# For MyISAM (if used)
ft_min_word_len = 2

# Disable built-in stop words (or specify a custom file)
innodb_ft_enable_stopword = OFF
# innodb_ft_server_stopword_table = 'mydb/my_stopwords'

After changing these parameters, you must rebuild the index: run OPTIMIZE TABLE articles; or recreate the FULLTEXT index.

Ngram Parser for Cyrillic and CJK

The standard built-in parser handles many languages poorly: it doesn't understand morphology and relies on whitespace as a delimiter. For substring search and better multilingual support, the ngram parser is recommended — available since MySQL 5.7.

# my.cnf
[mysqld]
ngram_token_size = 2

-- Creating an index with the ngram parser
CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(512) NOT NULL,
  description TEXT,
  FULLTEXT idx_ngram (name, description) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Substring search
SELECT id, name
FROM products
WHERE MATCH(name, description) AGAINST('smartph' IN BOOLEAN MODE);

The ngram index breaks text into all possible n-grams of a given size. With ngram_token_size = 2, the word "Moscow" produces bigrams: "mo", "os", "sc", "co", "ow". This enables searching by any substring, but increases index size by 3–5× compared to the standard parser.

Stop Words

MySQL ships with a list of English stop words. For non-English projects you should either disable the default list or plug in a custom one:

-- Create a stop words table
CREATE TABLE mydb.stopwords (
  value VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO mydb.stopwords VALUES
  ('the'), ('a'), ('an'), ('in'), ('on'), ('for'), ('of'), ('from');

-- In my.cnf, point to the table:
-- innodb_ft_server_stopword_table = 'mydb/stopwords'

Practical SQL Examples for Complex Search Scenarios

Search with Filtering and Relevance Sorting

SELECT
  p.id,
  p.name,
  p.price,
  MATCH(p.name, p.description) AGAINST(:query IN BOOLEAN MODE) AS relevance
FROM products p
WHERE
  p.category_id = :category_id
  AND p.active = 1
  AND MATCH(p.name, p.description) AGAINST(:query IN BOOLEAN MODE)
ORDER BY relevance DESC, p.created_at DESC
LIMIT :limit OFFSET :offset;

Field Boosting: Title Matters More Than Description

SELECT
  id,
  title,
  (
    MATCH(title) AGAINST(:query IN BOOLEAN MODE) * 3 +
    MATCH(body) AGAINST(:query IN BOOLEAN MODE)
  ) AS boosted_score
FROM articles
HAVING boosted_score > 0
ORDER BY boosted_score DESC
LIMIT 20;

Note: this trick requires two separate FULLTEXT indexes — one on title and one on body.

Fuzzy Search via SOUNDEX (a Workaround)

-- Rough approximation: FTS + SOUNDEX filter
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST(:query IN BOOLEAN MODE)
   OR SOUNDEX(title) = SOUNDEX(:query)
LIMIT 20;

MySQL's SOUNDEX function is oriented toward English and works poorly with non-Latin scripts. For fuzzy search in other languages, consider Elasticsearch or preprocessing on the PHP/Laravel side.

Relevance and Ranking: How MySQL Computes the Score

In IN NATURAL LANGUAGE MODE, MySQL calculates the score using a formula close to TF-IDF: the more frequently a word appears in a document (TF) and the less frequently across the entire collection (IDF), the higher its weight. Documents in which the search term appears in more than 50% of rows receive a zero score and are not returned — a common pitfall with small datasets (fewer than 20 rows).

To improve ranking quality in MySQL, several techniques are used:

• Split indexes by fields with different weights (title, tags, body) and sum scores with coefficients.
• Store a pre-computed document "boost" (rating, view count) and multiply it by the FTS score.
• Use IN BOOLEAN MODE for explicit weight control via the > and < operators.

-- Explicit boost using the > operator in BOOLEAN MODE
SELECT id, title
FROM articles
WHERE MATCH(title, body)
  AGAINST('(>MySQL 

// Connecting Scout to a model (Laravel)
use Laravel\Scout\Searchable;

class Article extends Model
{
    use Searchable;

    public function toSearchableArray(): array
    {
        return [
            'id'       => $this->id,
            'title'    => $this->title,
            'body'     => $this->body,
            'category' => $this->category?->name,
        ];
    }

    // For MySQL FTS via scout-mysql-driver
    public function searchableAs(): string
    {
        return 'articles'; // table / index name
    }
}

// Searching via Scout (driver is transparently swappable)
$results = Article::search('MySQL full-text search')
    ->where('active', 1)
    ->orderBy('published_at', 'desc')
    ->paginate(20);

[mysqld]
# Buffer size for InnoDB FTS indexes
innodb_ft_cache_size = 32000000
innodb_ft_total_cache_size = 640000000

# Minimum token size
innodb_ft_min_token_size = 2

# Ngram: bigram size
ngram_token_size = 2

# Disable built-in stop words
innodb_ft_enable_stopword = OFF

# Log slow queries (including FTS)
slow_query_log = ON
long_query_time = 0.1
log_queries_not_using_indexes = ON