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.

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;

Frequently Asked Questions