Medium Database

MySQL Full-Text Search vs LIKE: FULLTEXT Indexes, Boolean Mode, Vietnamese Text and Meilisearch

LIKE '%term%' scans every row and ignores relevance. Use MySQL FULLTEXT indexes, boolean mode, ngram and Laravel whereFullText, and know when to move on.

20 Nov, 2026 9 min Updated 23 Oct, 2026 9 Code blocks
Diagram
graph TD A[Search requirement] --> B{Only match the start of a field} B -->|yes| C[LIKE prefix with B-tree index] B -->|no| D{Typos, facets or synonyms needed} D -->|no| E[MySQL FULLTEXT index] E --> F[ngram parser for CJK text] D -->|yes| G[Meilisearch or Elasticsearch via Scout]

The product search box worked fine at launch with 2,000 products. A year later the catalogue has 400,000 rows, and every keystroke in the search box fires a query that takes 1.9 seconds. The slow query log is full of WHERE title LIKE '%tai nghe%' OR description LIKE '%tai nghe%'. Worse, users complain that searching "tai nghe khong day" finds nothing, because the product says "Tai nghe không dây", and searching "headphones wireless" misses "wireless headphones".

LIKE is a pattern matcher, not a search engine. It cannot use an index for a leading wildcard, it knows nothing about words or relevance, and it compares exact characters. MySQL has had a real full-text engine in InnoDB since 5.6, and Laravel has first-class support for it. It is often all you need before reaching for a separate search service.

Why LIKE Does Not Scale

A B-tree index is sorted by the column's value from the first character. That makes LIKE 'tai nghe%' an index range scan: find the first entry starting with "tai nghe" and read forward. LIKE '%tai nghe%' gives the engine no starting point, so it reads every row and runs a substring comparison on each one.

-- Leading wildcard: no B-tree index can help, every row is read and compared
SELECT id, title FROM products
WHERE title LIKE '%tai nghe%' OR description LIKE '%tai nghe%'
ORDER BY id DESC
LIMIT 20;

-- Prefix only: can use an index on title, but only finds titles that START with the term
SELECT id, title FROM products WHERE title LIKE 'tai nghe%' LIMIT 20;

EXPLAIN on the first query shows type: ALL. The cost grows linearly with the table and multiplies with each ORed column. On top of that, the results are in id order, not by how well they match, so a product with "tai nghe" buried in paragraph six of the description ranks the same as one called exactly "Tai nghe".

How FULLTEXT Indexes Work

A FULLTEXT index is an inverted index: a dictionary of every word (token) in the indexed columns, each pointing to the rows that contain it. Searching for three words becomes three dictionary lookups and a merge, instead of a scan. InnoDB also keeps term statistics, so it can compute a relevance score based on how often a word appears in a row and how rare it is across the table.

ALTER TABLE products ADD FULLTEXT INDEX products_title_description_fulltext (title, description);

-- Natural language mode: relevance-ranked, rows come back sorted by score
SELECT id, title,
       MATCH(title, description) AGAINST('wireless noise cancelling headphones') AS score
FROM products
WHERE MATCH(title, description) AGAINST('wireless noise cancelling headphones')
LIMIT 20;

Two details trip people up:

  • The column list in MATCH(...) must exactly match the column list of one FULLTEXT index. MATCH(title) will not use an index on (title, description); create a separate index if you need both.
  • In natural language mode, when MATCH is in the WHERE clause and you do not add an ORDER BY, rows come back sorted by relevance. Selecting the same MATCH expression as score is free; MySQL computes it once.

Here is how to choose between the options covered in this tip:

graph TD A[Search requirement] --> B{Only match the start of a field} B -->|yes| C[LIKE prefix with B-tree index] B -->|no| D{Typos, facets or synonyms needed} D -->|no| E[MySQL FULLTEXT index] E --> F[ngram parser for CJK text] D -->|yes| G[Meilisearch or Elasticsearch via Scout]

Natural Language Mode vs Boolean Mode

Natural language mode (the default) treats the search string as a bag of words. Any row containing any of the words matches, ranked by relevance. It is forgiving and good for "type something, see the best matches" boxes. WITH QUERY EXPANSION runs the search twice, adding words from the top results the second time. It helps for very short queries and adds a lot of noise otherwise.

Boolean mode gives you operators and no automatic sorting:

-- Boolean mode: + must contain, - must not contain, * prefix, "..." exact phrase
SELECT id, title,
       MATCH(title, description) AGAINST('+headphone* +wireless -refurbished' IN BOOLEAN MODE) AS score
FROM products
WHERE MATCH(title, description) AGAINST('+headphone* +wireless -refurbished' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 20;

SELECT id, title FROM products
WHERE MATCH(title, description) AGAINST('+"tai nghe" +bluetooth' IN BOOLEAN MODE);
Operator Meaning
+word must be present
-word must not be present
word* prefix match: headphone* matches headphones
"two words" exact phrase, words adjacent and in order
>word <word increase or decrease that word's contribution
no operator optional, raises the score if present

For a typical search box, "every word must appear, prefixes allowed" (+word* for each term) behaves the way users expect, so boolean mode with that transformation is the usual production choice. Always add ORDER BY score DESC in boolean mode, since it does not sort for you.

Vietnamese and CJK Text

Full-text search depends on how text is split into tokens. The default parser splits on spaces and punctuation, which works for English and also for Vietnamese, because Vietnamese writes syllables separated by spaces. There are two Vietnamese-specific traps.

Short syllables are dropped. InnoDB ignores tokens shorter than innodb_ft_min_token_size, which defaults to 3. A huge share of Vietnamese syllables have one or two letters: "đi", "ăn", "cà", "xe", "ô". Searching "xe máy" in boolean mode with +xe* finds nothing because "xe" was never indexed. Lower innodb_ft_min_token_size to 2 (or 1) in my.cnf, restart, and rebuild the FULLTEXT indexes. Check the default stopword list too (INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD) since it is English-oriented.

Accents. Users type "tai nghe khong day" as often as "tai nghe không dây". Rather than depend on collation details, store a normalized search_text column with accents removed and search that. Laravel's Str::ascii() converts "Tai nghe không dây" to "Tai nghe khong day", including "đ" to "d".

Chinese, Japanese and Korean do not put spaces between words, so the default parser sees a whole sentence as one token. MySQL ships an ngram parser for this: it indexes every run of ngram_token_size characters (default 2), so "東京都" is indexed as "東京" and "京都". The ngram parser ignores innodb_ft_min_token_size and uses ngram_token_size instead.

-- my.cnf, needs a restart, then rebuild the index
-- [mysqld]
-- innodb_ft_min_token_size = 2
-- ngram_token_size = 2

ALTER TABLE products DROP INDEX products_title_description_fulltext;

ALTER TABLE products
    ADD FULLTEXT INDEX products_search_ngram (title, description) WITH PARSER ngram;

SELECT id, title FROM products
WHERE MATCH(title, description) AGAINST('"東京"' IN BOOLEAN MODE);

ngram also works on Vietnamese, but it indexes two-letter fragments across word boundaries, so results get noisy and the index is much larger. For Vietnamese, the default parser with a lower minimum token size and an unaccented column is usually the better combination.

Implementing It in Laravel

The schema builder has fullText() for indexes, and the query builder has whereFullText() / orWhereFullText(), which compile to MATCH ... AGAINST on MySQL. First the migration, adding the normalized column next to the index on the original columns:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('products', function (Blueprint $table) {
            $table->text('search_text')->nullable()->after('description');
            $table->fullText(['title', 'description']);
            $table->fullText('search_text');
        });
    }

    public function down(): void
    {
        Schema::table('products', function (Blueprint $table) {
            $table->dropFullText(['title', 'description']);
            $table->dropFullText(['search_text']);
            $table->dropColumn('search_text');
        });
    }
};

Keep search_text in sync from the model, so every save path updates it:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Str;

final class Product extends Model
{
    protected static function booted(): void
    {
        static::saving(function (Product $product) {
            $product->search_text = Str::of($product->title . ' ' . strip_tags((string) $product->description))
                ->ascii()
                ->lower()
                ->squish()
                ->value();
        });
    }
}

Then a search service that turns user input into a safe boolean-mode query. User input must be sanitized: a stray -, * or unbalanced " changes the meaning of the query or makes MySQL reject it.

namespace App\Search;

use App\Models\Product;
use Illuminate\Contracts\Pagination\Paginator;
use Illuminate\Support\Str;

final class ProductSearch
{
    private const OPERATORS = ['+', '-', '<', '>', '(', ')', '~', '*', '"', '@'];

    public function search(string $input, int $perPage = 20): Paginator
    {
        $terms = collect(preg_split('/\s+/u', Str::of($input)->ascii()->lower()->value(), -1, PREG_SPLIT_NO_EMPTY))
            ->map(fn (string $word) => str_replace(self::OPERATORS, '', $word))
            ->filter(fn (string $word) => mb_strlen($word) >= 2)
            ->unique()
            ->take(8);

        if ($terms->isEmpty()) {
            return Product::query()->latest('id')->simplePaginate($perPage);
        }

        $against = $terms->map(fn (string $word) => "+{$word}*")->implode(' ');

        return Product::query()
            ->select('*')
            ->selectRaw('MATCH(search_text) AGAINST(? IN BOOLEAN MODE) AS score', [$against])
            ->whereFullText('search_text', $against, ['mode' => 'boolean'])
            ->orderByDesc('score')
            ->orderByDesc('id')
            ->simplePaginate($perPage);
    }
}

whereFullText accepts ['mode' => 'boolean'] for boolean mode and ['expanded' => true] for query expansion. There is no relevance ordering helper, so the score comes from a selectRaw with a binding and is used in orderByDesc. Capping terms guards against pasted paragraphs.

Full-text behaviour depends on the real engine, so these tests need MySQL, not SQLite. Point your test database at a MySQL container in CI. One more gotcha: InnoDB only adds rows to the FULLTEXT index on commit, so tests that use RefreshDatabase (which wraps each test in a transaction) will not see their own inserts. Use DatabaseTruncation or DatabaseMigrations for the search test file.

use App\Models\Product;
use App\Search\ProductSearch;

beforeEach(function () {
    Product::factory()->create(['title' => 'Tai nghe Bluetooth chống ồn', 'description' => 'Pin 30 giờ']);
    Product::factory()->create(['title' => 'Loa Bluetooth mini', 'description' => 'Chống nước IPX7']);
    Product::factory()->create(['title' => 'Tai nghe có dây', 'description' => 'Jack 3.5mm']);
});

it('finds products without accents and ranks the best match first', function () {
    $results = app(ProductSearch::class)->search('tai nghe bluetooth');

    expect($results->pluck('title')->all())->toBe(['Tai nghe Bluetooth chống ồn']);
});

it('matches word prefixes', function () {
    $results = app(ProductSearch::class)->search('blue');

    expect($results)->toHaveCount(2);
});

it('treats boolean operators in user input as plain text', function () {
    $results = app(ProductSearch::class)->search('+loa -(bluetooth) "mini');

    expect($results->pluck('title')->all())->toBe(['Loa Bluetooth mini']);
});

it('falls back to latest products for empty input', function () {
    expect(app(ProductSearch::class)->search('  * + '))->toHaveCount(3);
});

The operator test matters most. Without sanitization, -(bluetooth) would exclude every Bluetooth product, and an unbalanced quote can produce a syntax error in AGAINST.

Common Pitfalls

  • MATCH columns not matching an index. You get Can't find FULLTEXT index matching the column list. Create an index for exactly the column set you query.
  • Short words silently ignored. The minimum token size and stopwords make queries for "xe", "go" or "it" return nothing, with no error. Tune innodb_ft_min_token_size and the stopword table for your language.
  • Expecting typo tolerance. "headphnoes" will not match "headphones". FULLTEXT has no fuzzy matching.
  • Testing inside a transaction. Uncommitted rows are not in the FULLTEXT index. Tests pass locally on SQLite with a LIKE fallback and fail in production, or the reverse.
  • Raw user input in boolean mode. Strip + - < > ( ) ~ * " @ or build the expression yourself.

When to Use LIKE, FULLTEXT or a Search Engine

LIKE is fine for prefix lookups on an indexed column (SKU, phone number, username autocomplete) and for small tables where a scan costs nothing.

MySQL FULLTEXT fits when:

  • you search words in titles and descriptions, a few hundred thousand to a few million rows;
  • "all words must appear, ranked by relevance" is good enough;
  • you want one database, transactional consistency, and no extra service to run.

Meilisearch, Typesense or Elasticsearch/OpenSearch, usually through Laravel Scout, are worth the extra moving part when:

  • users expect typo tolerance, synonyms, "did you mean";
  • you need facets with counts (brand, price range, rating) on every search;
  • ranking needs custom rules: boost in-stock items, recency, popularity;
  • you need language-aware analysis such as stemming, or search across tens of millions of documents.

Moving later is not a rewrite with Scout. Index a model by adding the trait and a searchable array:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Laravel\Scout\Searchable;

final class Article extends Model
{
    use Searchable;

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

Scout also ships a database engine that uses whereFullText for columns you mark with the SearchUsingFullText attribute, so you can start on MySQL behind the Scout API and switch the driver to Meilisearch when the requirements arrive. For the product search from the opening, a FULLTEXT index on an unaccented search_text column and a lower minimum token size took the query from 1.9 seconds to under 20 ms, and "tai nghe khong day" finally finds the wireless headphones.

Interactive challenge

Try the challenge

Practice what you just learned. Write your solution, reveal hints if you get stuck.

Instructions

Write to_boolean_query(input, minLength = 3) in PHP and toBooleanQuery(input, minLength = 3) in JavaScript. It turns raw search box input into a safe MySQL boolean-mode expression. First extract every complete double-quoted phrase and remove it from the input. For each phrase, replace the characters + - < > ( ) ~ * " @ with spaces, collapse runs of whitespace to one space, trim and lowercase it; if it is not empty, emit +"phrase". Then split the remaining text on whitespace; from each word remove those same characters, lowercase it, and emit +word* only if it has at least minLength characters (count characters, not bytes, so Vietnamese letters count as one). Output all phrases first, then all words, each group in input order, drop exact duplicates keeping the first, and join with single spaces. Empty or all-operator input returns an empty string. An unmatched quote is not a phrase: it is simply stripped from its word.

Starter code

php
function to_boolean_query(string $input, int $minLength = 3): string
{
    // TODO: extract complete "quoted phrases" first and remove them from the input
    // TODO: for each phrase: strip the characters + - < > ( ) ~ * " @, collapse whitespace,
    //       trim, lowercase; keep non-empty phrases as +"phrase"
    // TODO: split the rest on whitespace; strip the same characters from each word, lowercase,
    //       keep words with at least $minLength characters as +word*
    // TODO: phrases first, then words, both in input order, no duplicates, joined by one space
}
javascript
function toBooleanQuery(input, minLength = 3) {
  // TODO: extract complete "quoted phrases" first and remove them from the input
  // TODO: for each phrase: strip the characters + - < > ( ) ~ * " @, collapse whitespace,
  //       trim, lowercase; keep non-empty phrases as +"phrase"
  // TODO: split the rest on whitespace; strip the same characters from each word, lowercase,
  //       keep words with at least minLength characters as +word*
  // TODO: phrases first, then words, both in input order, no duplicates, joined by one space
  throw new Error('TODO');
}

Your solution

Hints

All hints revealed
Hint 1

Use the regex "([^"]*)" to find complete phrases, then replace those same matches with a space before splitting words.

Hint 2

In PHP use mb_strtolower and mb_strlen; in JavaScript count characters with [...word].length.

Hint 3

A Set in JavaScript or array_unique in PHP removes duplicates while keeping first occurrences in order.

Solution · php
function to_boolean_query(string $input, int $minLength = 3): string
{
    $strip = ['+', '-', '<', '>', '(', ')', '~', '*', '"', '@'];
    $parts = [];

    preg_match_all('/"([^"]*)"/u', $input, $matches);
    $rest = preg_replace('/"[^"]*"/u', ' ', $input);

    foreach ($matches[1] as $phrase) {
        $clean = mb_strtolower(trim(preg_replace('/\s+/u', ' ', str_replace($strip, ' ', $phrase))));

        if ($clean !== '') {
            $parts[] = '+"' . $clean . '"';
        }
    }

    foreach (preg_split('/\s+/u', $rest, -1, PREG_SPLIT_NO_EMPTY) as $word) {
        $clean = mb_strtolower(str_replace($strip, '', $word));

        if (mb_strlen($clean) >= $minLength) {
            $parts[] = '+' . $clean . '*';
        }
    }

    return implode(' ', array_values(array_unique($parts)));
}
Solution · javascript
function toBooleanQuery(input, minLength = 3) {
  const strip = /[+\-<>()~*"@]/g;
  const parts = [];

  for (const match of input.matchAll(/"([^"]*)"/g)) {
    const clean = match[1].replace(strip, ' ').replace(/\s+/g, ' ').trim().toLowerCase();
    if (clean !== '') parts.push(`+"${clean}"`);
  }

  const rest = input.replace(/"[^"]*"/g, ' ');
  for (const word of rest.split(/\s+/).filter(Boolean)) {
    const clean = word.replace(strip, '').toLowerCase();
    if ([...clean].length >= minLength) parts.push(`+${clean}*`);
  }

  return [...new Set(parts)].join(' ');
}

More from Database