Full-Text Search in SQL: Stopwords Hide 'ink' Products

Most production applications reach a point where LIKE '%search_term%' stops cutting it. You've got a product catalogue with 500,000 rows, a blog with a decade of articles, or a support ticket system with millions of entries — and users expect Google-quality search that returns relevant results in under 100 milliseconds. LIKE with a leading wildcard can't use a B-Tree index, so the database performs a full sequential scan every single time. At scale, that's a query that grinds your entire application to a halt.

Full-text search (FTS) solves this with an entirely different data structure — an inverted index — that maps individual tokens (words) back to the rows that contain them. But it does far more than just fast lookups. It understands language: it knows that 'running', 'ran', and 'runs' are forms of the same root word. It knows that 'the', 'a', and 'is' are so common they're noise. It can rank results by relevance so that the most useful rows float to the top. These capabilities live inside your existing SQL database, no Elasticsearch cluster required.

By the end of this article you'll understand exactly how an inverted index is built and queried, how to set up and tune full-text search in both MySQL and PostgreSQL, how relevance ranking actually works under the hood, when FTS is the right tool versus when you should reach for a dedicated search engine, and every production gotcha that bites teams who ship FTS without reading the fine print.

Why Full-Text Search in SQL Is Not Just LIKE with a Fancy Index

Full-text search in SQL is a specialized indexing and querying mechanism that tokenizes text into words (terms), stores them in an inverted index, and supports linguistic operations like stemming, ranking, and stopword filtering. Unlike a B-tree index used with LIKE '%pattern%', which scans every row and cannot use an index efficiently, a full-text index maps each term to its document locations, enabling sub-second searches over millions of rows.

At its core, full-text search builds an inverted index: for each unique word, it stores a list of document IDs and positions. Queries like CONTAINS or FREETEXT (T-SQL) or MATCH ... AGAINST (MySQL) leverage this index to find documents containing specific words or phrases, rank results by relevance using TF-IDF or BM25, and apply language-specific rules such as stemming (e.g., 'running' matches 'run') and stopword removal (common words like 'the' are ignored). This is fundamentally different from pattern matching — it's a set-based retrieval, not a scan.

Use full-text search when your application needs to search natural language text — product descriptions, articles, emails — at scale. It's the right tool when you need relevance ranking, linguistic variants, or fast search over large text columns. But it's not a replacement for a dedicated search engine like Elasticsearch when you need faceted search, fuzzy matching, or real-time indexing at massive scale. In SQL databases, full-text search fills the gap between simple LIKE queries and external search infrastructure.

How the Inverted Index Works Internally

An inverted index is the data structure behind all full-text search engines — SQL's built-in FTS included. It maps every distinct word (token) to the list of rows where that word appears, along with its position and frequency inside each document.

When you insert or update a row, the database tokenizes the text, discards stopwords, applies stemming, and builds (or appends to) this mapping. The result is a structure you can query in logarithmic time: look up the token, get the row list, apply ranking, return top N.

MySQL stores its FTS index in separate internal InnoDB tables (auxiliary tables). The main table holds the data; the FTS index is in hidden tables (INNODB_FTS_INDEX_TABLE, INNODB_FTS_INDEX_CACHE). You cannot query them directly for debugging, but they exist.

PostgreSQL uses GiST or GIN indexes, which store lexemes (stemmed tokens) directly in the index structure. GIN is faster for lookups (read-heavy), GiST is faster for updates (write-heavy). You can query the tsvector column directly for debugging.

How a query is executed: When you run MATCH(col) AGAINST('search'), the database: (1) tokenizes and stems the query text, (2) looks up each token in the inverted index, (3) retrieves posting lists (list of row IDs), (4) optionally intersects them for multi-word queries, (5) computes a relevance score (TF-IDF or BM25) for each candidate row, (6) sorts by score and returns top N.

-- MySQL: InnoDB FTS uses hidden auxiliary tables
-- You can inspect them (read-only)
SELECT * FROM INFORMATION_SCHEMA.INNODB_FTS_INDEX_TABLE;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FTS_INDEX_CACHE;

-- PostgreSQL: tsvector column stores lexemes
CREATE TABLE io_thecodeforge.documents (
    id SERIAL PRIMARY KEY,
    body TEXT,
    search_vector TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);

-- Insert and the tsvector is auto-generated
INSERT INTO io_thecodeforge.documents (body) VALUES ('The quick brown fox jumps over the lazy dog.');

-- Query the tsvector directly to see normalized terms
SELECT id, body, search_vector FROM io_thecodeforge.documents;

-- Output shows:
-- 'brown':2 'dog':8 'fox':3 'jump':4 'lazi':7 'quick':1
-- Stopwords ('the', 'over') are omitted. Stemming: 'jumps' → 'jump', 'lazy' → 'lazi'

MATCH/AGAINST Syntax and Relevance Ranking

The SQL syntax for full-text search is MATCH(columns) AGAINST('query' IN NATURAL LANGUAGE MODE). The engine returns rows ordered by relevance — a floating-point score. Higher scores mean more relevant.

Relevance ranking isn't magic. Behind the scenes the database computes a variant of TF-IDF (MySQL) or BM25 (PostgreSQL).

• TF-IDF (Term Frequency-Inverse Document Frequency) rewards terms that appear frequently in a document but rarely across the whole corpus. It doesn't normalise for document length, so long documents (with more words) tend to score higher simply because they contain more text.
• BM25 (Best Matching 25) adds document length normalisation. A short document that matches a term is given a higher boost relative to its length. It also saturates term frequency so that 100 occurrences of a word doesn't give 100x the score of 1 occurrence.

IN BOOLEAN MODE gives you operators: +mandatory, -exclude, *wildcard, "phrase". It doesn't rank — it just filters. Use it when you need exact control over which terms must match, not when you need relevance ordering.

WITH QUERY EXPANSION (MySQL) adds terms from top-ranked results to the original query. It can help find related documents but can also drift off-topic.

Practical example: For a product catalogue with descriptions of varying lengths (e.g., 50-character titles vs 5000-character detailed descriptions), BM25 gives fairer ranking. MySQL's TF-IDF would rank the long descriptions higher even if the title contains the exact match.

-- MySQL: Natural language mode (ranked)
SELECT id, title,
       MATCH(title, body) AGAINST('Docker Compose' IN NATURAL LANGUAGE MODE) AS relevance
FROM io_thecodeforge.articles
ORDER BY relevance DESC;

-- PostgreSQL: ts_rank with normalization
SELECT id, title,
       ts_rank(to_tsvector('english', title || ' ' || body),
               plainto_tsquery('english', 'Docker Compose'), 1) AS relevance
FROM io_thecodeforge.articles
ORDER BY relevance DESC;

-- Boolean mode with operators (MySQL/PostgreSQL)
SELECT id, title
FROM io_thecodeforge.articles
WHERE MATCH(title, body) AGAINST('+Docker +healthcheck -deprecated' IN BOOLEAN MODE);

-- Phrase search in PostgreSQL using <-> operator
SELECT id, title
FROM io_thecodeforge.articles
WHERE to_tsvector('english', body) @@ to_tsquery('docker <-> compose');

Stopwords, Stemming and Language Support

Stopwords are common words (the, a, in, of) that don't carry search meaning. FTS engines discard them by default. But the default stopword list is English-centric and often wrong for your domain. If you're searching a product catalogue and 'ink' is a valid product name, but 'ink' happens to be in your custom stopword list — you'll lose results. On PostgreSQL, the default list includes 'the', 'a', 'and', 'of', 'to', 'in', 'for', etc. Not 'ink', unless customised. But technical terms like 'set', 'key', 'host', 'port' are NOT in default lists, but could be added accidentally.

Stemming reduces words to root forms: 'running' becomes 'run', 'better' becomes 'good'. MySQL uses a built-in stemmer based on the language setting. PostgreSQL uses dictionaries (english, simple, snowball). You can create custom dictionaries for domain-specific terms.

Key trade-off: aggressive stemming conflates unrelated words (e.g., 'marketing' and 'market' become 'market') causing false positives. Too little stemming and you miss variants.

Language-specific considerations: MySQL supports per-column character sets and collations that affect tokenisation. For non-English languages (Chinese, Japanese, Korean), MySQL's ngram parser splits text into n-character substrings; PostgreSQL's parser uses language-specific dictionaries.

Custom dictionaries in PostgreSQL: You can chain dictionaries (e.g., a custom thesaurus that maps 'JS' to 'JavaScript', then a snowball stemmer). This is far more flexible than MySQL's global setting.

-- MySQL: set a custom stopword table
SET GLOBAL innodb_ft_server_stopword_table = 'mydb/my_stopwords';

CREATE TABLE mydb.my_stopwords (value VARCHAR(30));
INSERT INTO mydb.my_stopwords VALUES ('the'), ('a'), ('of'), ('our'), ('your');

-- Rebuild index for changes to take effect
OPTIMIZE TABLE mydb.articles;

-- PostgreSQL: create custom text search configuration
CREATE TEXT SEARCH CONFIGURATION io_thecodeforge.product_search (COPY = english);

-- Replace stemmer with 'simple' (no stemming) for precise matching
ALTER TEXT SEARCH CONFIGURATION io_thecodeforge.product_search
    ALTER MAPPING FOR word WITH simple;

-- Add a custom thesaurus dictionary
CREATE TEXT SEARCH DICTIONARY my_thesaurus (
    TEMPLATE = thesaurus,
    DictFile = 'product_synonyms'
);

-- Then use it in queries
SELECT to_tsvector('io_thecodeforge.product_search', 'JS framework');

Performance Tuning and Index Maintenance

Full-text indexes grow with your data and can become bloated, fragmented, or stale.

Practical benchmarks: On a 10 million row table with 1KB average text per row, MySQL InnoDB FTS index size is about 20-30% of data size (2-3GB). PostgreSQL GIN index is larger, about 40-50% (4-5GB), but provides faster read queries.

The staleness trap: After a bulk delete or update, FTS indexes may still point to deleted rows. Run CHECK TABLE (MySQL) or REINDEX (PostgreSQL) after any bulk operation affecting >20% of rows.

-- MySQL: Check FTS index fragmentation
SHOW INDEX FROM io_thecodeforge.articles;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FTS_INDEX_TABLE
WHERE TABLE_ID = (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = 'io_thecodeforge/articles');

-- Rebuild fulltext index (blocks writes) — schedule during maintenance
OPTIMIZE TABLE io_thecodeforge.articles;

-- Set min token length (requires restart)
SET GLOBAL innodb_ft_min_token_size = 2;

-- PostgreSQL: create index CONCURRENTLY (no downtime)
CREATE INDEX CONCURRENTLY idx_fts_articles ON io_thecodeforge.articles USING GIN(to_tsvector('english', body));

-- Monitor index size
SELECT pg_size_pretty(pg_relation_size('idx_fts_articles'));

-- Rebuild online without locking
REINDEX INDEX CONCURRENTLY idx_fts_articles;

-- Increase memory for index operations
SET maintenance_work_mem = '1024MB';  -- for REINDEX
SET work_mem = '64MB';  -- for query-time sorting

MySQL vs PostgreSQL: Implementation Differences That Matter

While both MySQL and PostgreSQL offer full-text search, their internals differ significantly. Choosing the wrong one can lead to performance pain or missing features.

Index storage: MySQL stores the FTS index in hidden auxiliary tables (INNODB_FTS_INDEX_TABLE, INNODB_FTS_INDEX_CACHE) managed by InnoDB. PostgreSQL stores it as a GiST or GIN index on a tsvector column.

Tokenisation and stemming: MySQL uses a built-in tokeniser and stemmer that are less configurable. You can change the minimum token length via innodb_ft_min_token_size and set a custom stopword table, but the stemmer is fixed per language (English, French, Spanish, etc.). PostgreSQL uses text search configurations that separate tokeniser (parser) and dictionary (stemmer, stopwords, thesaurus), giving you the ability to chain dictionaries and create custom ones.

Index maintenance: MySQL requires OPTIMIZE TABLE to rebuild the FTS index (table is locked). PostgreSQL supports CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY for zero-downtime rebuilds.

Query flexibility: PostgreSQL supports ranking weights per column using setweight and can store tsvector as a generated column. MySQL's ranking is less flexible; you can use multiple MATCH clauses with custom multipliers but it's less efficient.

Debugging: MySQL's FTS debugging requires querying INFORMATION_SCHEMA tables. PostgreSQL allows direct querying of the tsvector column, making debugging trivial.

Production recommendation: Use PostgreSQL if you need custom stemming, zero-downtime rebuilds, or fair ranking (BM25). Use MySQL if you're already in a MySQL environment and have simple text search requirements with moderate write rates.

-- MySQL: Creating an FTS index
ALTER TABLE io_thecodeforge.articles ADD FULLTEXT(title, body);

-- MySQL: Checking FTS stats (auxiliary tables)
SELECT * FROM INFORMATION_SCHEMA.INNODB_FTS_INDEX_TABLE;

-- MySQL: Rebuild FTS index (blocks writes)
OPTIMIZE TABLE io_thecodeforge.articles;

-- PostgreSQL: Creating GIN index on generated tsvector column
ALTER TABLE io_thecodeforge.articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
CREATE INDEX idx_articles_fts ON io_thecodeforge.articles USING GIN(search_vector);

-- PostgreSQL: Query the tsvector directly for debugging
SELECT id, search_vector FROM io_thecodeforge.articles;

-- PostgreSQL: Rebuild without blocking
REINDEX INDEX CONCURRENTLY idx_articles_fts;

Full-Text Search Queries: How They Actually Work at the Protocol Level

You've seen MATCH/AGAINST. Fine. But what happens when you type a query like '+smartphone -iphone warranty' in BOOLEAN MODE? The query parser breaks that string into tokens at the same word boundaries the indexer used. Then it traverses the inverted index looking for documents containing 'smartphone' while excluding those that also contain 'iphone'. The Boolean operators are evaluated as set operations on document ID lists.

Here's where juniors get burned: query expansion and proximity searches. MySQL's WITH QUERY EXPANSION performs a two-phase search — the first returns results, the second re-runs the query using the most relevant terms from the first result set. Sounds clever until you realise it can return garbage if your initial query matched irrelevant documents. Never use it on production user-facing search without manual relevance testing.

SQL Server's CONTAINSTABLE and FREETEXTTABLE return ranked results with internal scoring you can inspect. The difference? FREETEXT does linguistic stemming and thesaurus expansion automatically. CONTAINS gives you exact control over prefixes, proximity, and inflectional forms. Pick the right weapon.

// io.thecodeforge — database tutorial

-- MySQL: exact phrase with proximity
SELECT product_id, product_name
FROM products
WHERE MATCH(product_name, description)
AGAINST('"next day delivery"' IN BOOLEAN MODE);

-- SQL Server: proximity within 5 words
SELECT p.ProductID, p.Name
FROM Production.Product p
WHERE CONTAINS(p.Name, 'smartphone NEAR iphone', LANGUAGE 'English');

-- PostgreSQL: using tsquery operators
SELECT id, title
FROM documents
WHERE to_tsvector('english', content) @@
      to_tsquery('english', 'smartphone <-> iphone');
-- <-> means adjacent, <2> means within 2 words

The Full-Text Index Architecture Nobody Draws on the Whiteboard

The inverted index is the heart, but the rest of the body matters too. Every full-text index has three structural layers: the token list, the postings list, and the position list. The token list maps each unique word to a compressed integer ID. The postings list maps that ID to a list of document IDs where the word appears. The position list tracks word offsets within each document for proximity searches.

SQL Server stores these as internal system tables inside a full-text catalog — a virtual container that doesn't map to a single file. Each catalog can span multiple filegroups, and each index has its own population history. When you rebuild a full-text index, SQL Server drops all internal structures and re-parses every row through the filter daemon and word breaker. That's why a rebuild on a 10-million-row table takes hours, not seconds.

PostgreSQL stores tsvector directly in the table column. No separate catalog. This means updates are row-level — when you change a row, PostgreSQL re-parses only that row through a trigger or computed column. The trade-off: no incremental population like SQL Server's change tracking. You pay for every UPDATE with CPU to re-lex the text. Know your write pattern before choosing.

// io.thecodeforge — database tutorial

-- SQL Server: inspect full-text catalog details
SELECT c.name AS catalog_name,
       i.name AS index_name,
       i.is_active,
       i.crawl_type_description,
       i.item_count,
       i.unique_key_count
FROM sys.fulltext_catalogs c
JOIN sys.fulltext_indexes i ON c.fulltext_catalog_id = i.fulltext_catalog_id;

-- PostgreSQL: check tsvector stats per row
SELECT id,
       length(to_tsvector('english', content)) AS vector_size,
       nspname || '.' || relname AS table_name
FROM documents d
JOIN pg_class ON oid = d.tableoid
LIMIT 5;

PATINDEX and CHARINDEX — Pattern Matching Beyond LIKE in SQL Server

LIKE is fine for simple wildcards, but it chokes when you need position-aware substring searches without full-text indexing. PATINDEX and CHARINDEX fill that gap. CHARINDEX is your scalpel: it returns the starting position of one string inside another, zero if missing. No wildcards, no guesswork. PATINDEX steps it up with regex-like patterns — find any numeric digit, any non-alphanumeric character, or custom patterns using % and _ with square brackets. Why this matters: you can extract specific substrings from messy data without resorting to slow CLR functions or client-side parsing. Use them when full-text search is overkill but LIKE is too weak. Both ignore collation unless you force it, so watch case sensitivity. They're also non-SARGable — indexes won't help. Keep pattern matching lean.

// io.thecodeforge — database tutorial

-- Find position of 'urgent' (case-sensitive by collation)
SELECT CHARINDEX('urgent', body, 1) AS pos
FROM tickets
WHERE body LIKE '%urgent%';

-- PATINDEX: find first numeric digit position
SELECT PATINDEX('%[0-9]%', description) AS first_digit_pos
FROM products
WHERE PATINDEX('%[0-9]%', description) > 0;

-- PATINDEX with exclusion pattern: non-alpha start
SELECT PATINDEX('%[^a-zA-Z]%', title) AS first_non_alpha
FROM articles;