Useful Full-Text Search With Laravel and MySQL

Search often starts as a LIKE query and stays that way until the table becomes large enough to hurt. MySQL already provides full-text indexes, and Laravel exposes them through the query builder. For many product catalogues, knowledge bases, and admin tools, that is enough. The important part is knowing what full-text search does well, where it surprises people, and how to keep authorization inside the query.

Define the searches users are actually making

I separate exact lookup from text discovery. Order numbers, SKUs, email addresses, and known IDs need equality or prefix indexes. Titles, descriptions, notes, and article bodies are better candidates for full-text search. Asking one full-text query to handle both usually makes exact identifiers feel unreliable.

Before changing the schema, I collect a small set of real queries and the results people expect near the top. I include misspellings, short words, product codes, quoted phrases, common terms, and a query that should return nothing. That list becomes a repeatable relevance test instead of relying on whichever search happens to be tried during review.

Index the fields that form one searchable document

A FULLTEXT index works on CHAR, VARCHAR, and TEXT columns. I group columns that users search together, such as title and body, and keep structured filters such as workspace_id, status, category, and published_at as normal columns with appropriate indexes. The columns passed to the search query must match a usable full-text index.

Laravel migrations can create a full-text index directly. On a busy existing table, I inspect the SQL and test the operation against the real MySQL version first. Adding the first InnoDB full-text index can be a meaningful table operation, so I do not assume that a short migration file means a short or non-blocking production change.

Schema::table('articles', function (Blueprint $table) {
    $table->fullText(
        ['title', 'summary', 'body'],
        'articles_content_fulltext'
    );
});

Apply permissions before limiting results

Search is another read endpoint, so it needs the same tenant and visibility rules as a normal listing. I add those constraints to the database query before the result limit. Fetching a global top twenty and filtering forbidden rows afterward is both unsafe and incomplete because inaccessible rows can displace valid results.

I still authorize the selected record when the user opens it. Search constraints reduce the candidate set correctly, while the model policy remains the final resource boundary. Keeping both layers also protects against a future search refactor accidentally becoming the only authorization check.

$articles = Article::query()
    ->where('workspace_id', $user->workspace_id)
    ->where('is_published', true)
    ->whereIn('audience', $user->searchAudiences())
    ->whereFullText(
        ['title', 'summary', 'body'],
        $request->string('q')->trim()->value(),
    )
    ->limit(20)
    ->get();

Know what the tokenizer leaves out

Full-text search is token based. Stopwords may be omitted, short tokens can fall below the configured minimum length, and a partial word is not the same as a prefix search. This matters for short product names, abbreviations, Dutch compound words, and identifiers. The exact behaviour depends on the database engine, version, parser, and configuration, so I verify it on the same setup used in production.

I do not silently fall back to LIKE ‘%query%’ across a large table when full-text search returns nothing. A leading wildcard normally prevents a regular B-tree index from helping. If short codes matter, I give them a dedicated normalized column and an exact or prefix query. If typo tolerance matters, I state that as a separate feature because native full-text search does not automatically provide a polished fuzzy-search experience.

• Test words near the minimum token length
• Check the active stopword configuration
• Keep exact identifiers on dedicated indexed columns
• Test the languages your users actually write
• Explain zero results instead of hiding them with an expensive scan

Combine predictable signals instead of inventing a magic score

For a small application, native relevance plus strong filters may already be good enough. When exact title matches should win, I add that rule explicitly. A normalized exact match, a title prefix, publication state, and recency are understandable product signals. I keep the ranking simple enough that another developer can explain why one result appeared above another.

Natural-language and boolean modes behave differently. I choose a MySQL search mode from the product need and keep queries parameterized through Laravel. User input must never be concatenated into raw MATCH or AGAINST expressions.

Measure quality and database cost together

I test relevance with known queries, but I also inspect the generated SQL, execution plans, latency distribution, result counts, and query volume. Search-as-you-type can generate far more requests than expected, so the UI should debounce input, require a sensible minimum length, and cancel stale requests. Server-side rate limits protect the endpoint from accidental and deliberate abuse.

My security test creates records with unique phrases in two workspaces, plus drafts and revoked content. Every search must exclude forbidden IDs before results reach PHP. My rollout test compares the old and new result sets for representative queries, then watches slow-query logs and database load after release. Relevance is important, but a search feature is not successful if it makes ordinary writes or page loads unstable.

Use this in practice

• Separate exact identifiers from text discovery
• Create a matching full-text index
• Apply tenant and visibility filters in SQL
• Test stopwords and short tokens on the production engine
• Keep raw user input out of SQL expressions
• Measure relevance, query plans, and database load
• Test forbidden records with unique search phrases