Jan Mikoláš
Glossary
Full-text search
Full-text search goes beyond matching a literal substring. It prepares text in a search index, processes the query, and scores the results according to defined relevance rules.
Short definition
Searching the meaningful parts of text.
Full-text search examines a title, description, or complete document using words and their normalised forms. Unlike an exact filter, it can consider several words, their order, and their significance within a collection, assigning each result a score.
It can run directly in a relational database, in a separate system such as Elasticsearch, or through a combination of both. Elasticsearch is one possible implementation, not a synonym for full-text search.
What it solves
Finding useful content without knowing the exact stored wording.
Users often know neither a product’s exact name nor the form in which a word appears in its description. Full-text search turns a free-form query into searchable units and lets the application return and rank several approximate matches.
- searching products by name, description, brand, and category
- searching articles, documentation, tickets, or internal records
- finding several words without requiring one literal, contiguous string
- matching an exact phrase or word prefix, with carefully configured typo tolerance where useful
- ranking by relevance and producing a short highlighted excerpt containing the matched terms
Practical example
The query “black winter jacket” in an e-commerce catalogue.
A product document contains a name, description, SKU, brand, and category. The name and description are analysed as natural language, while the brand and category may also have an exact-value representation for filtering. The SKU is processed separately: hyphens, slashes, and combinations of letters and numbers do not behave like ordinary English words, and language stemming could make them harder to find.
The analyser splits “black winter jacket” into the tokens black, winter, and jacket, then normalises them using the same rules as the indexed documents. Depending on the chosen English-language processing, it may fold case or reduce related word forms. Diacritic folding, stop-word removal, and stemming or lemmatisation are not mandatory steps: each should be enabled only when testing on real queries shows that it improves results.
The search engine finds documents containing matching tokens in the index. Scoring may give more weight to a match in the title than the description, to a greater number of matched words, to a rarer term, or to tokens appearing close together. The result is not objectively “best”; it is an ordering produced by the chosen relevance function, field weights, and any business rules, all of which need validation against real searches.
Exact filters can be applied at the same time, such as a price between CZK 2,000 and CZK 5,000 and an in-stock requirement. A filter does not express textual similarity and generally does not alter the score; it simply removes candidates that do not qualify. Before a purchase, the application verifies price and availability in the authoritative database because a separate search index may briefly lag behind it.
How it works
Documents → analyser → index; query → analysis → relevance → results.
This text flow also serves as an alternative to a diagram: documents and user queries must be prepared using compatible basic rules so that their tokens can be compared.
- Documents The application chooses searchable fields and distinguishes natural language from exact codes, brands, prices, and states.
- Analyser A tokenizer splits text into tokens; filters may lowercase them or transform diacritics, stop words, and word forms.
- Full-text index An inverted structure maps each token to documents and may retain the frequencies and positions needed for phrases and scoring.
- Query analysis A multi-word query is processed compatibly with the index, and query rules decide whether all words, some words, or an exact phrase must match.
- Matching, scoring, and filters The index finds candidates, the relevance function scores them, and exact conditions such as price, brand, or availability narrow the result set.
- Results The application sorts items by score or another selected order and may generate a highlighted excerpt; the score itself says nothing about the correctness of business data.
Key components and principles
The index, analysis, and query design all determine result quality.
Full-text search is not a single universal algorithm. Different fields and query types need rules suited to the language and to how users actually search.
Tokenisation and normalisation
A token is a searchable unit, most commonly a word. The tokenizer determines its boundaries, while normalisation may, for example, make letter case consistent. Each language requires deliberate handling of accents and diacritics; without testing, one cannot assume that every analyser will connect accented and unaccented spellings correctly.
Analyser and language
An analyser is a pipeline consisting of a tokenizer and subsequent filters. Stop words may remove very common words. Stemming reduces words according to rules, while lemmatisation finds their dictionary form; neither technique guarantees linguistically correct results on its own, and overly aggressive settings merge unrelated terms.
Full-text and ordinary database indexes
An ordinary database index, such as a B-tree, maps a complete value or an ordered part of it to rows. A full-text index generally maps analysed tokens to documents. A specialised full-text index may still be stored inside a database, for example as a GIN index over a textual representation in PostgreSQL.
Multiple words, phrases, prefixes, and fuzzy matching
A query may require every word, allow only some, or boost the score as more words match. Exact phrase search uses token order and positions. A prefix matches the beginning of a token. Fuzzy matching tolerates a limited number of character edits, but it is optional, more expensive, and returns unrelated results when configured too broadly.
Relevance and scores
A score is a numerical estimate of how well a document matches a particular query. It may account for term frequency, rarity, document length, position, and field weight. Tuning relevance requires representative queries, expected results, and measurements; a higher number is not a general assessment of document quality.
Full-text search, filters, and sorting
Full-text search answers which documents match textually and how strongly. An exact brand, availability, or numeric-range filter makes only a yes-or-no decision. Results may be sorted by relevance, but also by price or date, deliberately replacing or combining the original score order.
Benefits, limitations, and common mistakes
Better search adds a data model, tuning work, and operational responsibility.
Practical benefits
- fast multi-word search over a prepared text collection
- ranking by measurable relevance instead of an arbitrary row order
- separating analysed text from exact filters and identifiers
- the option to add phrases, prefixes, highlighting, or cautious typo tolerance as needed
Limitations and mistakes
- treating full-text search as the same thing as LIKE or an ordinary B-tree index
- deploying a generic analyser without testing the target language, diacritics, and product codes
- declaring the first score-based ranking to be objectively the best result set
- enabling broad fuzzy matching for every short query
- ignoring index updates, failed writes, and divergence from the primary database
Choosing an implementation
Start with the required behaviour, not a technology name.
The SQL expression LIKE '%text%' searches for a literal pattern in the original string. It performs neither language analysis nor ordinary relevance scoring, and a leading wildcard often prevents use of a regular B-tree. It may still be perfectly adequate for a small table or simple administration interface; full-text search is not a mandatory replacement for every LIKE.
PostgreSQL provides tsvector, tsquery, ranking, and specialised indexes; MySQL has FULLTEXT indexes queried with MATCH … AGAINST; and SQLite offers the FTS5 extension. Database-native full-text search may be the simplest solution that meets the requirements when its language features, relevance, and operational characteristics suit the product.
A separate Elasticsearch deployment is useful for a larger index, custom analysers, combining text search with filters and aggregations, or scaling search independently. It also adds synchronisation, monitoring, and reindexing. The primary database remains authoritative for prices, inventory, and orders, so a combined design must account for temporary inconsistency.
What to consider
Search must be tested as both a user-facing and a data feature.
A correct answer is more than low latency. The system must return the expected products, update its index safely, and clearly separate search from authoritative business state.
- maintain a set of target-language queries covering diacritics where relevant, multiple words, phrases, and misspellings
- measure relevance separately for the name, description, brand, category, and exact SKU
- define whether multiple words mean AND, OR, or a minimum number of matches
- monitor indexing lag, indexing failures, and document counts against the primary database
- provide idempotent updates and a safe procedure for a complete reindex
- verify critical data against the authoritative source before displaying a price or allowing a purchase
Common questions
Full-text search in databases and applications.
Is full-text search the same as SQL LIKE '%text%'?
No. LIKE compares a string with a pattern. Full-text search works with analysed tokens and a specialised index, and can calculate relevance, match phrases, and apply other text rules.
Does full-text search always require Elasticsearch?
No. PostgreSQL, MySQL, and SQLite all provide full-text capabilities. A separate search system makes sense only when its features and scalability justify the extra operation and synchronisation.
Why do accented and unaccented queries return different results?
The analyser used for both indexing and queries determines this behaviour. Diacritics can be folded, but the setting needs testing because normalisation may also merge terms that should remain distinct.
Is the result with the highest score always the best?
No. A score expresses the chosen relevance model for that query. Field weights, language rules, and product signals need tuning and validation against user expectations.
Why does a newly changed product not appear immediately?
In a separate index, the update may still be queued, waiting for refresh, or have failed. The application should monitor the delay, be able to retry the update, and verify critical data in the primary database.
How I work with databases in practice
I design the search model together with its source data and update process.
In e-commerce and integration applications, I separate authoritative data from the search view and validate relevance against real queries.