“We need search” frequently becomes “we need Elasticsearch”, which becomes a cluster to run, a second copy of your data to keep in sync, and a new way for production to break. For a great many applications, the database already installed will do the job.

This is what MySQL full-text search actually gives you, the defaults that catch people out, and the honest boundary where you should stop and use a real search engine.

What is wrong with LIKE

SELECT * FROM tasks WHERE description LIKE '%invoice%';

Two problems, and the second is the one that matters.

It cannot use an index. A leading wildcard means every row is examined. On a hundred thousand rows that is a full scan on every keystroke of a search box.

It is not search, it is substring matching. Searching for “invoices” does not match “invoice”. Searching for “pay invoice” matches nothing unless those words are adjacent in that order. There is no ranking, so a row where the term appears once in a footnote ranks the same as one where it is the title.

What a full-text index does instead

It builds an inverted index: for each word, the list of rows containing it. That makes word lookup fast, and it makes ranking possible.

ALTER TABLE tasks ADD FULLTEXT INDEX ft_tasks (title, description);

SELECT id, title,
       MATCH(title, description) AGAINST('invoice generation' IN NATURAL LANGUAGE MODE) AS score
FROM tasks
WHERE MATCH(title, description) AGAINST('invoice generation' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

Two things come free that LIKE cannot do. It matches rows containing either word, ranking those with both higher. And the score is a value you can sort by, combine with other signals, or threshold on.

The column list in MATCH() must match the index definition exactly, in the same order. A query naming (description, title) against an index on (title, description) does not use it and quietly falls back to a scan, which is a surprisingly common reason for “full-text search is slow”.

The four defaults that surprise people

Words shorter than three characters are ignored

innodb_ft_min_token_size defaults to 3, so searching for “QA”, “UI” or a two-letter product code returns nothing. Changing it requires rebuilding the index, so decide early whether short tokens matter for your data.

Common words are dropped

There is a stopword list, and it is aggressive for technical content. Words like “where”, “when” and “there” are removed from both the index and the query. Searching for “where clause” searches only for “clause”.

For a product where those words are meaningful, set your own stopword table — possibly an empty one — and rebuild.

Natural language mode ignores your operators

Typing +invoice -draft in natural language mode searches for those characters as part of words. To get operators you need boolean mode, which is a different and more literal thing.

SELECT * FROM tasks
WHERE MATCH(title, description)
      AGAINST('+invoice -draft' IN BOOLEAN MODE);

• +word — must be present
• -word — must be absent
• word* — prefix match, which is what a search-as-you-type box needs
• "exact phrase" — adjacent words in order

Boolean mode has no relevance ranking of its own by default, and it does not apply the 50% rule below. For most applications it is the right choice, because user expectations about a search box are closer to boolean than to natural language.

The 50% rule

In natural language mode with a MyISAM table, a word appearing in more than half the rows is treated as noise and matches nothing. On a table of support tickets, searching for “error” can return zero results for exactly this reason — which looks like a broken feature rather than a design decision.

InnoDB does not apply this rule, and boolean mode does not either. It is worth knowing because it explains a class of bug report that is otherwise baffling.

Making it good enough for a real search box

The bare query above is a starting point. Four additions cover most of what users expect.

Prefix matching for search-as-you-type

<?php
$terms = collect(preg_split('/\s+/', trim($q)))
    ->filter()
    ->map(fn ($t) => '+' . addslashes($t) . '*')   // every term required, prefix matched
    ->implode(' ');

// "inv gen" becomes "+inv* +gen*"

This gives the behaviour people actually want from a search box: results narrow as they type, and every word they have typed must appear.

Combine relevance with your own signals

SELECT id, title,
       MATCH(title, description) AGAINST(? IN BOOLEAN MODE) * 1.0
     + (status = 'open') * 2.0
     + (updated_at > NOW() - INTERVAL 30 DAY) * 1.5   AS rank
FROM tasks
WHERE MATCH(title, description) AGAINST(? IN BOOLEAN MODE)
  AND organization_id = ?
ORDER BY rank DESC

Pure text relevance is rarely what a user means by “best match”. Recency, status and ownership usually matter more, and blending them is a few lines rather than a feature request to another system.

Weight the title above the body

MySQL cannot weight columns within one index. The workaround is two indexes and two scores added together — one on the title, one on the body — with the title’s contribution multiplied. Slightly more query, considerably better results.

Always filter by tenant, and mind the order

In a multi-tenant application the organisation filter is not optional. Be aware that MySQL may apply the full-text match first and then filter, so a term common across all tenants can do a lot of work before narrowing. Test with realistic data volumes across several tenants rather than one.

What about PostgreSQL?

If you are on PostgreSQL rather than MySQL, the equivalent is stronger and worth knowing about before reaching for a cluster.

A tsvector column with a GIN index gives you stemming out of the box — “running” matches “run” — configurable dictionaries, weighting per field built in rather than faked with two indexes, and ranking functions that account for term position. The pg_trgm extension adds trigram similarity on top, which covers typo tolerance: “invoce” can match “invoice” with a similarity threshold.

That combination closes two of the six gaps listed below, which moves the boundary meaningfully. Teams on PostgreSQL can usually go considerably further before a dedicated search engine earns its place.

Where it genuinely runs out

Where it genuinely runs out

Being honest about the boundary is the point of the article. Reach for a dedicated search engine when you need:

• Typo tolerance. “invoce” matching “invoice”. MySQL has nothing for this, and users expect it.
• Faceted search with live counts per filter across a large result set.
• Stemming and synonyms beyond the basics — “running” matching “run”, “laptop” matching “notebook”.
• Search across many tables or services as one ranked result set.
• Tens of millions of documents, or very high query volume.
• Serious relevance tuning as an ongoing activity with somebody measuring it.

If none of those are on your list, the database you already operate is probably sufficient, and the honest comparison is not “which is better at search” but “is the gap worth a cluster, a sync pipeline, and a second system that can be down while the database is up”.

The middle option people forget

Between MySQL full-text and a full search cluster there is a third answer that suits a lot of applications: a purpose-built search table in the same database.

One table, one row per searchable thing, with a denormalised text column, the tenant id, a type, and whatever you rank on. A full-text index on the text column, and triggers or model events keeping it current.

CREATE TABLE search_index (
    id              BIGINT PRIMARY KEY AUTO_INCREMENT,
    organization_id BIGINT NOT NULL,
    searchable_type VARCHAR(40) NOT NULL,
    searchable_id   BIGINT NOT NULL,
    title           VARCHAR(255) NOT NULL,
    body            MEDIUMTEXT,
    updated_at      TIMESTAMP,
    UNIQUE KEY (searchable_type, searchable_id),
    KEY (organization_id),
    FULLTEXT KEY ft (title, body)
) ENGINE=InnoDB;

This gets you cross-entity search — tasks, projects and comments in one ranked list — without leaving the database, and without keeping a second datastore in sync across a network. It is also trivially consistent, because the index is updated in the same transaction as the record.

Keeping the search table current

The separate search table is only useful if it reflects reality, and that is where these implementations usually rot.

Three approaches, in increasing order of reliability:

• Model events. Update the row whenever the record is saved or deleted. Simple, and it misses everything that does not go through the model — bulk imports, raw queries, console commands, migrations.
• Database triggers. Catches every write regardless of origin, at the cost of logic living in the schema where nobody looks for it.
• A periodic reconciliation. A nightly job that finds records whose updated_at is newer than their index row and re-indexes them. Slower to converge, and it corrects whatever the other two missed.

Model events plus the nightly reconciliation is the combination we would choose: fast in the normal case, self-healing in the abnormal one. The reconciliation is also what lets you change the indexed text — adding a field to the searchable body is a schema-free change that the job applies over a night.

One detail worth getting right early: index deletions too. An orphaned search row is worse than a missing one, because it produces a result that leads to a 404 — and users interpret that as the search being broken rather than the record being gone.

Measuring whether the search is any good

Search quality is easy to argue about and easy to measure, and almost nobody measures it.

1. Log every query and whether anything was clicked. A query with no click is a failure, and the list of them is the most useful document you will have for improving relevance.
2. Count zero-result queries. These are the clearest signal there is. A recurring zero-result term usually names a stopword problem, a stemming problem or a feature people expect you to have.
3. Watch the position of the clicked result. If people routinely click the fourth result, the ranking is wrong in a way no amount of opinion in a meeting will reveal.

This costs one table and a few lines, and it converts relevance tuning from taste into a numbers exercise. It is also the thing that tells you honestly whether you have outgrown the database — a rising zero-result rate that is mostly typos is a real argument for a search engine, in a way that “Elasticsearch is better” is not.

Operational notes

• Build the index after loading data, not before. Adding a full-text index to a populated table is much faster than inserting into an indexed one.
• Full-text indexes slow writes, like any index, and rather more than most. Keep them off hot transactional tables where you can — which is another argument for a separate search table.
• Watch the index size. It can rival the table. Index the fields people search, not every text column you have.
• Escape user input for boolean mode. A stray +, -, *, ( or @ changes the query’s meaning or makes it a syntax error. Strip or escape the operator characters before building the term string.
• Handle an empty result deliberately. If every term was a stopword, the query returns nothing and the user sees a blank page. Detect it and say so, rather than implying there were no matches.

A realistic sequence

If you are adding search to an application that has none, the order that avoids wasted work:

1. Start with a full-text index on the one table people ask about most. An afternoon, and it answers the question for a surprising number of products.
2. Add query logging immediately, before tuning anything. You will want the zero-result list within a week.
3. Move to a search table when somebody asks to search across two entity types at once. That request is the signal, and it always arrives.
4. Blend in recency and status once people complain that the right answer is not first.
5. Only then evaluate a search engine — with the zero-result log in hand, so the decision is based on your data rather than on a benchmark.

Most applications stop at step three or four and never need the last one. The ones that do need it arrive there with a clear case and a list of exactly which queries are failing, which makes the migration a much smaller and better-specified piece of work than it would have been at the start.

The short version

The short version

• LIKE '%x%' is a table scan and is not search.
• A full-text index gives word matching and a relevance score you can sort by.
• Boolean mode is usually what a search box should use.
• Watch the minimum word length, the stopword list, the 50% rule and the exact column order.
• Build terms as +word* for search-as-you-type, and escape the operators.
• Blend text relevance with recency, status and ownership.
• A separate search table gives cross-entity search without a second system.
• Move to a search engine for typos, facets, synonyms or tens of millions of rows — not before.

The general principle is worth more than the syntax: check whether the thing you already run can do it before adding a thing you do not. Every additional system in a stack is something that can be down, out of sync, out of date or misconfigured at three in the morning.

Related: MySQL indexes explained, and finding N+1 queries before your customers do.