SQL 95 🛢️ Full-Text Search and Indexing
Full-text search is the database feature that lets users find documents by their content rather than by exact column values. A LIKE '%term%' query scans every row and matches character patterns, which is slow and linguistically naive. Full-text search instead builds an inverted index: a mapping from words to the rows that contain them, with language-aware processing that stems words to their roots, removes common stopwords, and ranks results by relevance . When a user searches for “riding,” a full-text index can match “ride,” “rides,” and “rode” because it understands word forms, not just character sequences .
Every major relational engine has a native full-text implementation, but the syntax and capabilities differ significantly. PostgreSQL uses tsvector and tsquery types with GIN indexes. MySQL uses FULLTEXT indexes and MATCH() AGAINST() syntax. SQL Server uses full-text catalogs with CONTAINS and FREETEXT predicates. Oracle Text uses CONTEXT indexes and the CONTAINS operator. SQLite uses the FTS5 virtual table extension. This chapter covers the core mechanics of each, the indexing strategies that make them fast, and the patterns for combining full-text search with relational filters.
Key point: Full-text search works by parsing documents into tokens, normalizing them into lexemes, and building an inverted index that maps each lexeme to the documents containing it; query performance depends entirely on creating the right index type and querying the indexed column rather than calling the parsing function inline.
Why full-text search exists
The LIKE performance problem. WHERE content LIKE '%database%' cannot use a standard B-tree index because the leading wildcard prevents prefix matching. The database must scan every row, retrieve the full text, and run a substring match. On a table with millions of documents, this is unusable. A full-text index stores the tokens separately, so the query becomes an index lookup for the term database rather than a scan of every document .
The linguistic problem. LIKE matches exact characters. It does not know that “running” and “runs” share a root, that “the” and “and” are too common to be useful in a search index, or that a document where “database” appears in the title is more relevant than one where it appears in a footnote. Full-text search handles all three: stemming reduces words to their root forms, stopword lists remove common words, and ranking functions like ts_rank() or RANK score documents by term frequency and weight .
The document structure problem. A relational column contains a single value. A document often has structure: a title, a body, keywords, an abstract. PostgreSQL’s setweight() function lets you label lexemes with weights A, B, C, or D, so that a match in the title outranks a match in the body . SQL Server’s CONTAINSTABLE returns a RANK column that can be used to order results by relevance . Without this, every match is equally good, and the user sees the least relevant result first.
The multi-column problem. A search across title, body, and tags should treat all three as one searchable corpus, but a standard index is per-column. PostgreSQL solves this by concatenating the tsvector values from multiple columns into a single stored column and indexing that column with GIN . MySQL’s MATCH() accepts a comma-separated list of columns, and the FULLTEXT index must include exactly those columns .
a. PostgreSQL: tsvector, tsquery, and GIN
PostgreSQL’s full-text search is built on two types. tsvector is a sorted list of lexemes with their positions in the document. tsquery is a search query in the same normalized form, supporting operators like & (AND), | (OR), ! (NOT), and <-> (followed by) .
The to_tsvector() function parses a document and produces a tsvector. It removes stopwords, stems words to their roots, and records positions:
SELECT to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats');
-- 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4The output shows that “a”, “on”, and “it” were removed as stopwords, “rats” became “rat”, and the punctuation was ignored . The to_tsquery() and plainto_tsquery() functions convert a user query into a tsquery; plainto_tsquery() is more forgiving and treats all words as ANDed, while to_tsquery() allows explicit operators .
The match operator is @@:
SELECT * FROM posts WHERE search_vector @@ to_tsquery('english', 'postgresql & indexing');For performance, the tsvector must be stored in a column and indexed with GIN. Calling to_tsvector() inline in the WHERE clause defeats the index and forces a full scan . The standard pattern is a generated column or a trigger that maintains the tsvector column:
ALTER TABLE posts ADD COLUMN search_vector tsvector;
UPDATE posts SET search_vector = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);setweight() labels lexemes by source, so a title match can outrank a body match:
UPDATE posts SET search_vector =
setweight(to_tsvector(coalesce(title,'')), 'A') ||
setweight(to_tsvector(coalesce(body,'')), 'D');The ts_rank() function scores results, and ts_headline() highlights matching terms in the output .
b. MySQL: FULLTEXT indexes and MATCH() AGAINST()
MySQL’s full-text search requires a FULLTEXT index on the columns to be searched. The index can be created in CREATE TABLE or added later with ALTER TABLE or CREATE INDEX. It supports CHAR, VARCHAR, and TEXT columns in InnoDB or MyISAM tables .
Searching uses MATCH() AGAINST():
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database indexing' IN NATURAL LANGUAGE MODE);MATCH() must list exactly the columns that are in the FULLTEXT index. AGAINST() takes the search string and an optional modifier. The default is IN NATURAL LANGUAGE MODE, which treats the search as a free-text phrase, applies the stopword list, and returns rows ordered by relevance .
IN BOOLEAN MODE enables operators: + requires a word, - excludes it, * is a wildcard, and "..." is a phrase. This gives more control than natural language mode:
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+database -oracle' IN BOOLEAN MODE);The third mode is WITH QUERY EXPANSION, which performs a natural language search and then adds the most relevant terms from the results to the query, performing a second search. This can improve recall but may reduce precision .
MySQL’s FULLTEXT index has a limitation that matters for performance: it is much faster to load data into a table without a FULLTEXT index and then create the index afterward than to load data into a table with an existing index . For large imports, this is the recommended approach.
c. SQL Server, Oracle, and SQLite
SQL Server uses full-text catalogs and indexes managed by the Full-Text Engine. The query predicates are CONTAINS for precise word and phrase matching, and FREETEXT for meaning-based search that automatically handles inflections . CONTAINS(Name, 'mountain') finds exact matches; FREETEXT(Name, 'riding bikes') finds “ride,” “rides,” and “biking” . The table-valued functions CONTAINSTABLE and FREETEXTTABLE return a RANK column for relevance ordering . SQL Server’s full-text index is built on an inverted index of tokens, with stopwords removed and language rules applied .
Oracle Text uses CONTEXT indexes and the CONTAINS operator. A CONTEXT index is created on a single text column with INDEXTYPE IS CTXSYS.CONTEXT . The index tokenizes the document, removes stopwords, and stores token positions. Queries use CONTAINS(text, 'search terms') > 0 . Oracle Text supports multiple index types: CONTEXT for full-text, CTXCAT for mixed full-text and structured queries, and CTXRULE for document classification . The SCORE() operator returns a relevance score, analogous to PostgreSQL’s ts_rank() .
SQLite uses the FTS5 extension, which creates a virtual table rather than an index on an existing table. The virtual table stores the searchable content and its index together, or it can be configured as an external-content table that references the source table and relies on triggers to stay in sync . Queries use the MATCH operator:
CREATE VIRTUAL TABLE posts_fts USING fts5(title, body, content=posts, content_rowid=id);
SELECT * FROM posts_fts WHERE posts_fts MATCH 'database AND indexing';FTS5 supports phrase search with "...", prefix search with *, and NEAR for proximity . For external-content tables, the triggers must be configured correctly; a common mistake is using an AFTER UPDATE trigger when a BEFORE UPDATE trigger is required to remove the old tokens before the new values are indexed .
Complete Example Session
The following session builds a PostgreSQL full-text search over a posts table, creates a GIN index, and demonstrates ranking and highlighting.
-- ============================================
-- PART 1: CREATE TABLE AND SAMPLE DATA
-- ============================================
CREATE TABLE posts (
id serial PRIMARY KEY,
title text NOT NULL,
body text NOT NULL
);
INSERT INTO posts (title, body) VALUES
('PostgreSQL Indexing', 'Learn how to create GIN indexes for full text search.'),
('MySQL Full-Text', 'MySQL uses MATCH AGAINST for full text queries.'),
('Database Performance', 'Indexing is critical for database performance and full text search.');
-- ============================================
-- PART 2: ADD TSVECTOR COLUMN AND POPULATE
-- ============================================
ALTER TABLE posts ADD COLUMN search_vector tsvector;
UPDATE posts SET search_vector =
setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
setweight(to_tsvector('english', coalesce(body,'')), 'D');
-- ============================================
-- PART 3: CREATE GIN INDEX
-- ============================================
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);
-- ============================================
-- PART 4: SEARCH WITH TSQUERY
-- ============================================
SELECT id, title
FROM posts
WHERE search_vector @@ to_tsquery('english', 'indexing & database');
-- ============================================
-- PART 5: RANK RESULTS
-- ============================================
SELECT id, title,
ts_rank(search_vector, to_tsquery('english', 'indexing')) AS rank
FROM posts
WHERE search_vector @@ to_tsquery('english', 'indexing')
ORDER BY rank DESC;
-- ============================================
-- PART 6: HIGHLIGHT MATCHES
-- ============================================
SELECT id, title,
ts_headline('english', body, to_tsquery('english', 'indexing')) AS snippet
FROM posts
WHERE search_vector @@ to_tsquery('english', 'indexing');
-- ============================================
-- PART 7: PHRASE SEARCH
-- ============================================
SELECT id, title
FROM posts
WHERE search_vector @@ phraseto_tsquery('english', 'full text search');
-- ============================================
-- PART 8: VERIFY INDEX USAGE
-- ============================================
EXPLAIN ANALYZE
SELECT * FROM posts WHERE search_vector @@ to_tsquery('english', 'indexing');
-- Expect Bitmap Index Scan on idx_posts_search
-- ============================================
-- PART 9: TRIGGER TO MAINTAIN TSVECTOR
-- ============================================
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector :=
setweight(to_tsvector('english', coalesce(NEW.title,'')), 'A') ||
setweight(to_tsvector('english', coalesce(NEW.body,'')), 'D');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER posts_search_update
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION update_search_vector();
-- ============================================
-- PART 10: CLEANUP
-- ============================================
DROP TABLE posts;The ten parts cover table creation, tsvector population with weights, GIN indexing, tsquery search, ranking, highlighting, phrase search, index verification, trigger maintenance, and cleanup. What the session demonstrates is that full-text search performance depends on a stored tsvector column and a GIN index—calling to_tsvector() inline in the WHERE clause would bypass both.
Quick Reference
Full-Text Implementation by Engine
| Engine | Index Type | Query Syntax |
|---|---|---|
| PostgreSQL | GIN on tsvector | @@, to_tsquery, plainto_tsquery |
| MySQL | FULLTEXT | MATCH() AGAINST() |
| SQL Server | Full-text catalog | CONTAINS, FREETEXT |
| Oracle | CONTEXT index | CONTAINS(column, 'term') > 0 |
| SQLite | FTS5 virtual table | MATCH |
PostgreSQL Key Functions
| Function | Purpose |
|---|---|
to_tsvector(config, text) | Parse document to lexemes |
to_tsquery(config, query) | Parse query with operators |
plainto_tsquery(config, query) | Simple AND query |
setweight(vector, 'A') | Label lexemes by source |
ts_rank(vector, query) | Relevance score |
ts_headline(text, query) | Highlight matches |
MySQL Search Modes
| Mode | Behavior |
|---|---|
IN NATURAL LANGUAGE MODE | Free text, stopwords, relevance |
IN BOOLEAN MODE | Operators: +, -, *, "..." |
WITH QUERY EXPANSION | Two-pass search with related terms |
Indexing Rules
| Engine | Rule |
|---|---|
| PostgreSQL | Store tsvector, create GIN index |
| MySQL | Create FULLTEXT index on exact MATCH columns |
| SQL Server | Populate catalog, query indexed columns |
| SQLite | Create FTS5 virtual table, use triggers for sync |
Best Practices
✅ Do This:
ALTER TABLE posts ADD COLUMN search_vector tsvector; -- ✅ stored column
CREATE INDEX idx ON posts USING GIN(search_vector); -- ✅ GIN index
SELECT * FROM posts WHERE search_vector @@ to_tsquery('english', 'term'); -- ✅ indexed query
MATCH(title, body) AGAINST('term' IN BOOLEAN MODE); -- ✅ MySQL boolean❌ Don’t Do This:
SELECT * FROM posts WHERE to_tsvector('english', body) @@ to_tsquery('term'); -- ❌ full scan
MATCH(title) AGAINST('term') -- if index has (title, body) -- ❌ column mismatch
CONTAINS(Name, 'mountain') -- without full-text index -- ❌ failsCommon Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| PostgreSQL query slow | to_tsvector() inline, no index | Store tsvector, GIN index |
MySQL MATCH returns nothing | FULLTEXT index missing or column mismatch | Create index on exact columns |
| SQLite FTS5 out of sync | Triggers missing or wrong timing | Use correct BEFORE/AFTER triggers |
| Stopwords missing from results | Common words not indexed | Expected behavior; search meaningful terms |
SQL Server CONTAINS fails | No full-text index on column | Create catalog and index |
Real-World Examples
1. PostgreSQL search with ranking
SELECT *, ts_rank(search_vector, query) AS rank
FROM posts, to_tsquery('english', 'database & index') query
WHERE search_vector @@ query ORDER BY rank DESC;2. MySQL boolean search
SELECT * FROM docs WHERE MATCH(content) AGAINST('+server -cloud' IN BOOLEAN MODE);3. SQL Server CONTAINSTABLE
SELECT p.*, ft.RANK FROM Products p
INNER JOIN CONTAINSTABLE(Products, Name, 'mountain') ft ON p.Id = ft.[KEY];4. Oracle CONTAINS
SELECT SCORE(1), id FROM docs WHERE CONTAINS(text, 'database', 1) > 0;5. SQLite FTS5
SELECT * FROM posts_fts WHERE posts_fts MATCH 'sqlite AND search';6. PostgreSQL phrase search
SELECT * FROM posts WHERE search_vector @@ phraseto_tsquery('english', 'full text');7. Highlight in PostgreSQL
SELECT ts_headline('english', body, query) FROM posts, to_tsquery('term') query;8. MySQL FULLTEXT creation
ALTER TABLE articles ADD FULLTEXT(title, body);9. SQL Server FREETEXT
SELECT * FROM Products WHERE FREETEXT(Name, 'riding bikes');10. SQLite FTS5 trigger sync
CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
INSERT INTO posts_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;Visual
Inverted Index Structure
┌──────────────────────────────────────────────┐
│ DOCUMENTS │
│ Doc 1: "database indexing" │
│ Doc 2: "database performance" │
│ │
│ INVERTED INDEX │
│ ┌───────────┬─────────────┐ │
│ │ Lexeme │ Documents │ │
│ ├───────────┼─────────────┤ │
│ │ databas │ 1, 2 │ │
│ │ index │ 1 │ │
│ │ perform │ 2 │ │
│ └───────────┴─────────────┘ │
│ │
│ Query "database" → lookup → Docs 1, 2 │
└──────────────────────────────────────────────┘PostgreSQL Full-Text Pipeline
┌──────────────────────────────────────────────┐
│ Document: "The cats are running" │
│ │ │
│ ▼ │
│ to_tsvector('english', ...) │
│ │ │
│ ▼ │
│ Parse → tokens → stopwords removed │
│ "cats" → "cat", "running" → "run" │
│ │ │
│ ▼ │
│ tsvector: 'cat':2 'run':4 │
│ │ │
│ ▼ │
│ GIN index stores lexeme → document mapping │
└──────────────────────────────────────────────┘Indexed vs Inline Query
┌──────────────────────────────────────────────┐
│ INDEXED (fast) │
│ WHERE search_vector @@ to_tsquery(...) │
│ │ │
│ ▼ │
│ GIN index lookup │
│ │ │
│ ▼ │
│ Matching rows returned directly │
│ │
│ INLINE (slow) │
│ WHERE to_tsvector(body) @@ to_tsquery(...) │
│ │ │
│ ▼ │
│ Parse every row's body │
│ │ │
│ ▼ │
│ Full table scan ❌ │
└──────────────────────────────────────────────┘MySQL MATCH Column Requirement
┌──────────────────────────────────────────────┐
│ FULLTEXT INDEX (title, body) │
│ │ │
│ MATCH(title) AGAINST('x') │
│ → ERROR: column list does not match index │
│ │ │
│ MATCH(title, body) AGAINST('x') │
│ → OK ✅ │
│ │
│ The MATCH() columns must exactly match │
│ the columns in the FULLTEXT index. │
└──────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Full-text purpose | Linguistic search over documents |
| Core structure | Inverted index: lexeme → documents |
| PostgreSQL types | tsvector, tsquery |
| PostgreSQL index | GIN on stored tsvector |
| MySQL syntax | MATCH() AGAINST() |
| MySQL index | FULLTEXT |
| SQL Server predicates | CONTAINS, FREETEXT |
| Oracle index | CONTEXT, query with CONTAINS |
| SQLite extension | FTS5 virtual table, MATCH |
| Ranking | ts_rank (PostgreSQL), RANK (SQL Server) |
Key takeaways:
- Full-text search uses an inverted index, not a scan. The index maps words to documents, making lookups fast regardless of table size .
- Store the parsed vector and index it. PostgreSQL requires a stored
tsvectorcolumn with a GIN index; callingto_tsvector()inline defeats the index . - MySQL’s
MATCH()columns must match theFULLTEXTindex exactly. A mismatch causes an error or returns no results . - Stemming and stopwords are automatic. “Running” matches “run,” and “the” is ignored. This is linguistic processing, not string matching .
- Ranking makes results useful.
ts_rank()andRANKscore documents by relevance, so the best match appears first . - Phrase and boolean modes give control. PostgreSQL uses
<->and&; MySQL uses"..."and+/-in boolean mode . - Triggers maintain the index in sync. For SQLite FTS5 external-content tables and PostgreSQL stored vectors, a trigger ensures new and updated rows are indexed .
Remember: Full-text search is not LIKE with extra syntax. It is a different indexing strategy: parse documents into normalized tokens, build an inverted index that maps each token to the documents containing it, and query that index rather than scanning the table. PostgreSQL, MySQL, SQL Server, Oracle, and SQLite each implement this with different syntax and different index types, but the principle is identical. The performance rule is universal: index the parsed form, not the raw text, and query the indexed column. The relevance rule is also universal: use ranking functions to order results by how well they match, and weight fields like titles higher than bodies when the document has structure.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!