Full-Text Index in SQL
Introduction
A full-text index is a specialized search structure for finding words, phrases, prefixes, linguistic variants, and relevant documents inside large text collections. Unlike an ordinary B-tree, which orders complete scalar values for equality, range, and prefix comparisons, full-text search analyzes document content and creates searchable term-to-document mappings.
This capability powers article search, product discovery, knowledge bases, customer-support tickets, job portals, defect repositories, and document-management systems. It can answer questions such as "which documents discuss database performance?" and rank stronger matches ahead of weaker ones.
There is no single portable full-text-index syntax. SQL Server, PostgreSQL, MySQL, Oracle Text, and SQLite FTS5 use different document representations, tokenizers, query languages, ranking formulas, update mechanisms, and DDL. This tutorial explains common concepts first and then presents each engine independently.
What Is a Full-Text Index?
A full-text index transforms text into searchable terms or lexemes and records which documents contain them. Implementations may also store term frequency, position, field weight, language, or other metadata. Search queries are analyzed through compatible linguistic rules and matched against this index.
The result is usually a set of document or row identifiers, often with a relevance score. The database joins those identifiers back to structured columns such as article title, publication date, tenant, category, or visibility status.
Why Full-Text Search Is Needed
A query such as content LIKE '%database performance%' normally scans text and looks for one literal character sequence. It does not automatically tokenize words, normalize inflected forms, ignore stop words, understand proximity, or rank documents. A leading wildcard also prevents an ordinary B-tree prefix seek in common designs.
Full-text indexing performs much of that analysis in advance. Search can then locate candidate documents through indexed terms instead of examining every text value at request time.
A Search Catalog Analogy
Imagine a library catalog that lists each important word and the books and page positions where it occurs. A reader can search "database optimization" and immediately identify relevant books instead of opening every volume. Frequently repeated common words may be omitted, while word forms can be normalized.
This resembles an inverted index. Actual databases add language-specific tokenization, transactional maintenance, permissions, ranking, and structured SQL filtering.
Full-Text vs B-Tree Index
| Capability | B-tree index | Full-text index |
|---|---|---|
| Exact scalar equality | Excellent | Depends on text semantics |
| Numeric or date ranges | Excellent | Not its primary purpose |
| Left-anchored string prefix | Often useful | Available through engine query syntax |
| Words inside long text | Poor with leading wildcards | Core purpose |
| Phrase and proximity | No linguistic model | Commonly supported |
| Stemming and stop words | No | Language-dependent |
| Relevance ranking | No native text relevance | Commonly supported |
How an Inverted Index Works
An inverted index maps terms to documents rather than documents to their complete text. If articles 101 and 103 contain "sql," the term entry records those document identifiers. A query for SQL can read that posting list directly.
Phrase search needs term positions, ranking may need frequencies and document length, and multi-field weighting needs field metadata. Implementations compress and merge these structures differently, but the term-to-document model explains the main efficiency gain.
Full-Text Processing Pipeline
Text usually passes through parsing, tokenization, normalization, dictionary or stemmer processing, stop-word filtering, and index insertion. Query text must pass through compatible analysis so its terms match indexed lexemes. A mismatch between document and query configuration can produce surprising missing results.
Search quality is therefore partly a data-model and language-configuration concern, not only an index-performance concern.
Important Terminology
- Document: one searchable row, record, file, or combined set of fields.
- Token: a unit identified by a tokenizer, often a word or n-gram.
- Lexeme: a normalized searchable form of a token.
- Posting list: document identifiers associated with one term.
- Position: term location used for phrase or proximity matching.
- Stop word: a term intentionally omitted from normal indexing.
- Stemmer: linguistic component that reduces word variants.
- Rank: a score estimating how strongly a document matches.
Sample Article Table
CREATE TABLE articles (
article_id BIGINT PRIMARY KEY,
title VARCHAR(250) NOT NULL,
summary TEXT,
content TEXT NOT NULL,
category_id INT NOT NULL,
language_tag VARCHAR(20) NOT NULL,
published_at TIMESTAMP NOT NULL,
is_published BOOLEAN NOT NULL
);
Full-text search can identify relevant article IDs, while normal columns enforce publication, tenant, language, date, and category rules. Search applications typically combine both kinds of predicates.
Tokenization
A tokenizer decides where terms begin and end. Punctuation, hyphens, apostrophes, URLs, email addresses, source-code symbols, and emoji can produce engine-specific tokens. Languages without whitespace boundaries often need n-gram or morphological tokenizers rather than a simple word breaker.
Inspect token output before deployment. If C++, ASP.NET, part numbers, or hyphenated product names are split incorrectly, search quality will suffer regardless of index speed.
Normalization and Stemming
Normalization can fold case, remove accents, apply dictionaries, or reduce related forms to one lexeme. An English stemmer may allow "optimize," "optimized," and "optimization" to match related concepts. The exact result depends on the configured engine and language.
Stemming is not universal semantic understanding. Domain terms, names, acronyms, and multilingual content often require custom dictionaries, synonyms, or application-level query expansion.
Stop Words
Stop words are common terms such as articles or conjunctions that an engine may omit to reduce index size and noise. A query made entirely of omitted words can return no matches or an empty query representation. Domain-specific words that look common may still matter, such as "to be" in a quotation archive.
Review and version stoplists deliberately. Changing them often requires rebuilding or repopulating the index.
Minimum and Maximum Token Length
Some engines or parsers ignore very short or very long terms. This matters for programming languages, model codes, abbreviations, medical terms, and product identifiers. MySQL parser settings and stop-word lists are common examples of configuration that can make expected words unsearchable.
Changing token-length settings may require index rebuilding. Test representative production vocabulary first.
Natural-Language Search
Natural-language mode interprets user text as ordinary words and calculates matching relevance. It is useful for broad discovery where users should not need Boolean operators. Ranking formulas consider engine-specific signals such as term frequency and document statistics.
Scores are meaningful primarily within one result set. Avoid treating them as permanent universal measurements or comparing values from different queries as if they shared one scale.
Boolean Search
Boolean search lets users require, exclude, combine, or group terms. Operators are not portable: MySQL uses its Boolean full-text syntax, PostgreSQL represents operators in tsquery, SQL Server uses its CONTAINS grammar, and SQLite FTS5 has another query language.
Never concatenate untrusted input directly into a search grammar. Parse or parameterize through supported interfaces and define which operators the application permits.
Phrase Search
Phrase search requires terms to occur in a specified sequence and often adjacent positions. It is more precise than requiring every word anywhere in a document. Stop-word positions and tokenization can influence matching, so a displayed phrase is not always represented as a simple token pair.
Prefix Search
A full-text prefix query finds terms beginning with a token prefix, such as optim*. This is different from a character wildcard applied to raw text. Prefix syntax, escaping, minimum length, and whether a quoted form is required differ by engine.
Leading wildcard or arbitrary substring search is not automatically efficient. N-gram tokenization, trigram indexes, or a dedicated search engine may be required.
Proximity Search
Proximity search finds terms within a specified distance or order. Documents where "database" appears near "performance" may rank above documents where the words occur in unrelated sections. SQL Server, PostgreSQL, Oracle Text, MySQL, and FTS5 expose different proximity capabilities and syntax.
Relevance Ranking
Ranking estimates match quality. Common inputs include term frequency, rarity, field weight, document length, position, and proximity. Applications usually combine text rank with business signals such as freshness, inventory, permissions, quality, or popularity.
Always add a deterministic secondary sort, such as publication date and ID, so pagination remains stable when several documents have equal rank.
Field Weighting
A title match commonly matters more than the same term deep in body content. Some systems support explicit weights, while others encourage separate columns or query expressions. PostgreSQL can label lexemes with weights; SQL Server supports weighted terms; external search platforms often provide richer field boosts.
Weights should be tuned from relevance judgments, not guessed once and forgotten.
MySQL Full-Text Index
CREATE FULLTEXT INDEX ft_articles_search
ON articles (title, summary, content);
MySQL supports FULLTEXT indexes for qualifying CHAR, VARCHAR, and TEXT columns in supported storage engines such as InnoDB and MyISAM. Parser, stop-word, token-length, character-set, and partitioning restrictions depend on engine and version.
MySQL Natural-Language Query
SELECT
article_id,
title,
MATCH(title, summary, content)
AGAINST('database performance' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, summary, content)
AGAINST('database performance' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC, article_id DESC;
The MATCH column list must correspond to a suitable full-text index. Natural-language mode is the default when no modifier is supplied, but writing it explicitly improves readability.
MySQL Boolean Query
SELECT article_id, title
FROM articles
WHERE MATCH(title, summary, content)
AGAINST('+database +performance -oracle' IN BOOLEAN MODE);
The leading plus requires a term and the minus excludes a term from otherwise matched rows. Boolean operators, quoting, proximity, wildcards, and scoring rules are MySQL-specific. An exclusion-only query does not mean "all rows except" in InnoDB Boolean search.
MySQL Phrase and Prefix Search
-- Phrase
MATCH(title, content)
AGAINST('"database performance"' IN BOOLEAN MODE)
-- Prefix token
MATCH(title, content)
AGAINST('optim*' IN BOOLEAN MODE)
Phrase and prefix interpretation depends on tokenization. An asterisk preserves a prefix term that might otherwise be too short or treated differently, but it does not provide arbitrary leading-wildcard search.
MySQL Query Expansion
Query expansion runs a natural-language search, derives additional terms from highly relevant results, and searches again. It can improve recall but also introduce topic drift, especially for short ambiguous queries. Evaluate it with curated relevance examples before exposing it broadly.
PostgreSQL Text Search Model
PostgreSQL represents an analyzed document as tsvector and a search request as tsquery. A vector stores normalized lexemes, optionally with positions and weights. A query combines lexemes through AND, OR, NOT, prefix, and phrase-distance operators.
GIN is the preferred general text-search index type. GiST is another option with different storage and false-match behavior.
PostgreSQL Search Vector
ALTER TABLE articles
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
setweight(to_tsvector('english', coalesce(content, '')), 'C')
) STORED;
CREATE INDEX ix_articles_search_vector
ON articles USING GIN (search_vector);
The generated column keeps document analysis aligned with writes. Explicit configuration avoids changes caused by a session's default text-search configuration.
PostgreSQL User Query
SELECT article_id, title
FROM articles
WHERE search_vector @@ websearch_to_tsquery(
'english', 'database performance -oracle'
);
websearch_to_tsquery accepts familiar web-style input and avoids syntax errors for many raw user strings. plainto_tsquery, phraseto_tsquery, and to_tsquery serve other levels of control.
PostgreSQL Phrase Search
SELECT article_id, title
FROM articles
WHERE search_vector @@ phraseto_tsquery(
'english', 'database performance'
);
The resulting query uses positional information and accounts for stop-word gaps. Direct tsquery phrase operators can express exact or specified lexeme distances.
PostgreSQL Ranking
WITH q AS (
SELECT websearch_to_tsquery(
'english', 'database performance'
) AS query
)
SELECT
a.article_id,
a.title,
ts_rank_cd(a.search_vector, q.query) AS rank
FROM articles AS a
CROSS JOIN q
WHERE a.search_vector @@ q.query
ORDER BY rank DESC, a.article_id DESC;
ts_rank and ts_rank_cd provide different ranking approaches and normalization options. Weight labels from the vector can make title matches contribute more strongly.
SQL Server Full-Text Architecture
SQL Server full-text search uses full-text catalogs and indexes populated through language-specific word breakers, stemmers, stoplists, and filters. A table needs a unique, non-null, single-column key index that the full-text index uses to identify rows.
Search uses CONTAINS and FREETEXT predicates or the ranked rowset functions CONTAINSTABLE and FREETEXTTABLE.
Creating a SQL Server Full-Text Index
CREATE FULLTEXT CATALOG ArticleSearchCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.articles (
title LANGUAGE 1033,
summary LANGUAGE 1033,
content LANGUAGE 1033
)
KEY INDEX PK_articles
WITH CHANGE_TRACKING AUTO;
Language identifier 1033 represents English in this example. Choose the language that matches each column's content so word breaking, stemming, thesaurus lookup, and noise-word handling are appropriate.
SQL Server CONTAINS
SELECT article_id, title
FROM dbo.articles
WHERE CONTAINS(
(title, summary, content),
'"database performance"'
);
CONTAINS supports precise word and phrase matching, prefixes, inflectional forms, proximity terms, Boolean composition, and weighting through its own grammar.
SQL Server FREETEXT
SELECT article_id, title
FROM dbo.articles
WHERE FREETEXT(
(title, summary, content),
'improving database performance'
);
FREETEXT analyzes ordinary text and matches forms and terms rather than requiring exact query grammar. It is useful when the user expresses a concept instead of constructing precise operators.
SQL Server Ranked Results
SELECT
a.article_id,
a.title,
ft.RANK
FROM CONTAINSTABLE(
dbo.articles,
(title, summary, content),
'database AND performance'
) AS ft
JOIN dbo.articles AS a
ON a.article_id = ft.[KEY]
ORDER BY ft.RANK DESC, a.article_id DESC;
The table-valued function returns keys and ranks. Add structured filters after the join and preserve a deterministic secondary order for pagination.
SQL Server Prefix and Proximity
CONTAINS represents a prefix term with quoted syntax such as "optim*". Custom proximity syntax can limit distance and order. These expressions belong to the SQL Server full-text grammar and should be built through validated application controls.
SQLite FTS5
CREATE VIRTUAL TABLE article_search USING fts5(
title,
summary,
content,
content='articles',
content_rowid='article_id'
);
FTS5 is a virtual-table module rather than an ordinary index declaration. Contentless and external-content designs have synchronization responsibilities. Tokenizer choices include Unicode and trigram-oriented options, subject to build and version support.
Querying SQLite FTS5
SELECT
rowid AS article_id,
title,
bm25(article_search) AS rank
FROM article_search
WHERE article_search MATCH '"database performance"'
ORDER BY rank, rowid DESC;
FTS5 supports phrases, prefixes, Boolean combinations, column filters, proximity-style groups, highlighting, snippets, and the bm25 ranking function. Its query grammar is not interchangeable with MySQL or SQL Server syntax.
Oracle Text
CREATE INDEX ix_articles_content
ON articles (content)
INDEXTYPE IS CTXSYS.CONTEXT;
SELECT
article_id,
title,
SCORE(1) AS relevance
FROM articles
WHERE CONTAINS(content, 'database AND performance', 1) > 0
ORDER BY SCORE(1) DESC, article_id DESC;
Oracle Text CONTEXT indexes support rich linguistic query grammar and relevance scores. Depending on synchronization configuration, changes may require or schedule CTX_DDL.SYNC_INDEX. Current Oracle documentation recommends CONTEXT for new text retrieval applications rather than deprecated CTXCAT usage.
Cross-Database Comparison
| Database | Primary structure | Typical query interface |
|---|---|---|
| SQL Server | Full-text catalog and index | CONTAINS, FREETEXT, CONTAINSTABLE |
| PostgreSQL | tsvector with GIN/GiST | tsquery and @@ |
| MySQL | FULLTEXT index | MATCH ... AGAINST |
| SQLite | FTS5 virtual table | MATCH and FTS5 functions |
| Oracle | Oracle Text CONTEXT index | CONTAINS and SCORE |
Full-Text Search vs LIKE
LIKE is appropriate for exact character patterns, small tables, codes, and left-anchored prefixes supported by B-trees. Full-text search is appropriate for tokenized natural-language documents, phrase and proximity conditions, stemming, and relevance.
They answer different questions. A full-text parser may not preserve punctuation or arbitrary substrings, while LIKE does not provide linguistic ranking.
Full-Text Search vs Regular Expressions
Regular expressions match character patterns and are useful for validation or structural extraction. They usually do not provide an inverted term index, stemming, stoplists, or document relevance. Some products offer trigram or specialized indexes that accelerate particular regex patterns, but that is separate from ordinary full-text semantics.
Full-Text Search vs Vector Search
Full-text search excels at lexical matching: words, phrases, prefixes, fields, and Boolean rules. Vector search represents semantic similarity through embeddings and can find conceptually related content without shared terms. Hybrid search combines lexical precision with semantic recall.
Vector search adds model, storage, freshness, evaluation, and privacy concerns. Use it only when measured relevance requires it.
INSERT and UPDATE Maintenance
New or changed text must be analyzed and added to the full-text structure. Some engines update transactionally or through automatic change tracking; others use background queues, merges, refreshes, or explicit synchronization. Search freshness can therefore lag base-table commits.
Measure indexing throughput, backlog, and user-visible delay during imports and bursts. Do not assume immediate consistency across engines.
DELETE Maintenance
Deleting a document removes or invalidates its postings. Cleanup and segment merging may occur asynchronously. Large retention operations can create index churn, logs, temporary files, and storage amplification.
Plan deletion and rebuild behavior with the specific engine rather than treating the full-text index like a small B-tree.
Storage and Memory
Index size depends on document count, vocabulary, language, positions, term frequency, fields, tokenization, and compression. Position-aware phrase search and multiple weighted fields increase metadata. Original text still occupies base-table or external storage.
Capacity planning must include build workspace, transaction logs, merge amplification, backups, replicas, and caches.
Language Configuration
Word breaking and stemming must match the document language. Applying an English stemmer to mixed-language content can corrupt terms and relevance. Per-row language may require separate configurations, columns, indexes, or application routing depending on the engine.
Test names, acronyms, compound words, diacritics, and languages without whitespace boundaries.
Synonyms and Thesauri
Users may expect "database" to find "DBMS" or product nicknames to find official names. SQL Server and Oracle Text offer thesaurus capabilities; PostgreSQL supports dictionaries and query rewriting; external platforms commonly provide synonym sets. MySQL may require application expansion or parser customization.
Synonyms can reduce precision. Version them and evaluate with real search judgments.
Search Highlighting and Snippets
Search pages often show highlighted terms and short contextual fragments. PostgreSQL provides headline generation, SQLite FTS5 offers highlight and snippet functions, and other systems provide different APIs. Escape generated markup before rendering and avoid leaking text users are not authorized to view.
Structured Filters and Security
Full-text matches must still obey tenant, row-level security, publication, classification, and permission predicates. Search rank never overrides authorization. Apply security filters in the database plan or through a verified search architecture that cannot expose unauthorized document IDs or snippets.
Tokens themselves may reveal sensitive content in index storage, backups, diagnostics, or external search replicas. Include the index in security and retention reviews.
Pagination
Offset pagination can become expensive and unstable as scores change. Prefer a deterministic order combining relevance with a unique tiebreaker, and use engine-supported top-N or keyset approaches where practical. Persisting rank across changing data is difficult because document statistics and index state can alter scores.
Measuring Search Quality
Latency alone does not make search successful. Build a relevance set containing representative queries, expected documents, prohibited results, languages, misspellings, and empty-result cases. Measure precision, recall, click-through, successful reformulation, and domain-specific usefulness.
Review search changes with content experts, not only database administrators.
Performance Optimization
Use the correct language and tokenizer, store reusable analyzed vectors where the engine recommends it, index only searchable fields, keep structured filters indexed separately, limit returned rows, and avoid recalculating document vectors during every query. Monitor build queues, merge work, cache, I/O, CPU, and ranking cost.
Explain plans and engine diagnostics should confirm that the text index is used. A function applied differently from the indexed expression can force per-row analysis.
When a Database Index Is Enough
Database full-text search is often a strong fit when the corpus lives in one database, transactional integration matters, query volume is moderate, language needs are supported, and search features remain focused. It simplifies permissions, joins, backups, and consistency compared with another distributed system.
When to Use a Search Platform
A dedicated platform may be justified for very large distributed corpora, advanced analyzers, typo tolerance, autocomplete, faceting, cross-source indexing, custom ranking, semantic or hybrid retrieval, and independent scaling. It introduces synchronization, eventual consistency, operations, security duplication, and failure recovery.
Choose from measured product requirements, not because full-text search sounds synonymous with a search cluster.
Website Article Search
Weight titles above summaries and body text, filter unpublished or unauthorized articles, and combine relevance with freshness carefully. Search logs can identify missing synonyms and no-result queries, but must be handled as potentially sensitive user data.
E-Commerce Product Search
Product search combines lexical relevance with availability, category, merchant, price, locale, and business ranking. Model numbers and SKUs may require exact B-tree lookup alongside full-text descriptions. Faceting and typo tolerance can push requirements toward a dedicated platform.
Customer Support Tickets
Agents search descriptions, comments, products, and error messages. Tenant and permission filters are mandatory. Technical tokens, stack traces, and case-sensitive codes may need a custom tokenizer or an exact companion index.
Software Defect Search
A defect repository can index title, reproduction steps, expected behavior, and actual behavior with different weights. Build number, component, status, severity, and environment remain structured filters. Avoid stemming identifiers that should remain exact.
Job Portal Search
Job search combines role, skills, location, authorization, seniority, and recency. Synonyms such as "SDET" and "test automation engineer" may improve recall, while required certifications and location constraints should remain explicit structured criteria.
Knowledge Base Search
Knowledge articles benefit from title weighting, phrase search, language routing, product-version filters, and access control. Retired articles should not outrank current guidance. Search evaluation should include whether users resolve tasks without opening support cases.
Common Mistakes
- Using full-text search for exact IDs, dates, or numeric ranges.
- Assuming one vendor's DDL and query grammar are portable.
- Ignoring language, tokenizer, stop-word, and token-length settings.
- Expecting arbitrary substring search from a word-based index.
- Concatenating untrusted input into Boolean query syntax.
- Ranking without deterministic secondary ordering.
- Ignoring index freshness and background merge behavior.
- Indexing sensitive content without security review.
- Returning unauthorized snippets or document identifiers.
- Measuring latency while ignoring relevance quality.
- Applying one stemmer to mixed-language documents.
- Choosing an external search cluster before requirements justify it.
Best Practices
- Define search behavior with representative user queries.
- Choose language, tokenizer, dictionaries, and stoplists deliberately.
- Keep exact identifiers and structured filters in ordinary indexes.
- Weight fields from relevance evidence rather than intuition alone.
- Validate phrase, prefix, proximity, exclusion, and empty-query behavior.
- Parameterize or safely parse search-language input.
- Apply authorization before exposing matches or snippets.
- Monitor indexing lag, merges, storage, CPU, and query latency.
- Maintain a relevance test set across schema and configuration changes.
- Document engine-specific syntax and rebuild procedures.
Interview Questions and Answers
How would you define a full-text index?
It is a specialized structure that maps analyzed text terms to documents so the database can perform linguistic word and phrase searches efficiently.
How does it differ from a B-tree?
A B-tree orders complete scalar keys for equality and ranges; full-text structures analyze content into terms, positions, and document postings.
What is an inverted index?
It maps each searchable term to the documents or rows containing that term.
What are tokenization and stemming?
Tokenization identifies searchable units, while stemming normalizes related word forms according to linguistic rules.
What are stop words?
They are terms intentionally omitted from ordinary indexing because they are considered too common or unhelpful under a configured stoplist.
Why can search results lag behind writes?
Some engines populate, synchronize, or merge full-text structures asynchronously rather than within the base-row commit.
Does full-text search replace LIKE?
No. Full-text search handles linguistic terms and relevance; LIKE handles literal character patterns and can suit exact prefixes or small data.
When is an external search system appropriate?
When requirements include distributed scale, advanced analyzers, typo tolerance, autocomplete, faceting, custom ranking, or cross-source and semantic retrieval.
Practical Exercises
- Inspect how your engine tokenizes technical terms and punctuation.
- Create a text index and compare it with a leading-wildcard scan.
- Test natural-language, Boolean, phrase, prefix, and exclusion queries.
- Compare results under two language configurations.
- Weight title and body differently, then evaluate ranking changes.
- Insert and update documents while measuring search freshness.
- Combine text search with tenant, publication, and date filters.
- Create a relevance set and score the quality of returned results.
Design Checklist
| Area | Review question |
|---|---|
| Corpus | Which fields and document types should be searchable? |
| Language | Which tokenizers, stemmers, stoplists, and synonyms apply? |
| Queries | Are phrase, prefix, proximity, Boolean, or typo features required? |
| Ranking | How should text relevance combine with freshness and business rules? |
| Security | Can unauthorized terms, snippets, or IDs leak? |
| Freshness | How much indexing delay can users tolerate? |
| Operations | What are build, merge, backup, and rebuild costs? |
| Quality | Which judged queries will detect relevance regressions? |
Quick Reference
-- MySQL
CREATE FULLTEXT INDEX ft_articles_search
ON articles (title, summary, content);
-- PostgreSQL
CREATE INDEX ix_articles_search_vector
ON articles USING GIN (search_vector);
-- SQL Server
CREATE FULLTEXT INDEX ON dbo.articles (
title LANGUAGE 1033,
content LANGUAGE 1033
)
KEY INDEX PK_articles;
-- SQLite
CREATE VIRTUAL TABLE article_search
USING fts5(title, summary, content);
-- Oracle Text
CREATE INDEX ix_articles_content
ON articles (content)
INDEXTYPE IS CTXSYS.CONTEXT;
Key Takeaway
A full-text index is a linguistic retrieval structure, not a wider ordinary index. It analyzes documents into terms, records term-to-document relationships, and supports capabilities such as phrases, prefixes, stemming, proximity, and relevance. Search quality depends on language configuration, tokenization, field weighting, structured filters, and representative evaluation as much as raw query speed.
Design against the actual database engine because SQL Server, PostgreSQL, MySQL, SQLite, and Oracle use different structures and grammars. Keep exact identifiers in B-trees, protect every result with authorization, monitor indexing freshness and storage, and test relevance continuously. Move to an external search platform only when product requirements outweigh the synchronization and operational complexity it introduces.