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
MATCHis in theWHEREclause and you do not add anORDER BY, rows come back sorted by relevance. Selecting the sameMATCHexpression asscoreis free; MySQL computes it once.
Here is how to choose between the options covered in this tip:
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.
Testing Search
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
MATCHcolumns not matching an index. You getCan'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_sizeand 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
LIKEfallback 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.
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
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
}
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 revealedUse the regex "([^"]*)" to find complete phrases, then replace those same matches with a space before splitting words.
In PHP use mb_strtolower and mb_strlen; in JavaScript count characters with [...word].length.
A Set in JavaScript or array_unique in PHP removes duplicates while keeping first occurrences in order.
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)));
}
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(' ');
}

