Full Text Search Basics

Why this matters

As a Data Analyst, you often search messy text: feedback, tickets, survey responses, product names, and docs. Full-text search (FTS) gives you fast, relevant results with stemming, stopwords, phrases, and ranking—far beyond simple LIKE '%word%'.

• Filter customer feedback by topics: "refund", "billing issue", or exact phrases like "credit card declined".
• Build dashboards where users search knowledge base articles and see the most relevant first.
• Create cohorts (e.g., users who mentioned "shipping delay") for impact analysis.

Who this is for

• Data Analysts who query text columns and need better relevance and speed than LIKE.
• Analysts preparing to work with product analytics, support analytics, or search features.

Prerequisites

• Comfort with basic SQL (SELECT, WHERE, ORDER BY).
• Basic understanding of indexes.

Concept explained simply

Full-text search turns text into a special searchable format and uses an index to find relevant rows quickly. It understands words, ignores common ones (stopwords like "the"), and can match variations (stem/lemmatize) like "run" vs. "running". It can also rank results by relevance.

Mental model

Think of a book index at the back: each word points to pages. FTS builds a smart index of words to rows. Queries look up words, combine rules (AND/OR/NOT, phrases), and score rows by how well they match.

Core terms and operators

• Index: Special structure that makes text searches fast (e.g., GIN in PostgreSQL, FULLTEXT in MySQL, Full-Text Index in SQL Server).
• Document: The text field(s) you search (e.g., title + body).
• Stopwords: Very common words often ignored (e.g., "a", "the").
• Stemming/Lemmatization: Match variations of a word ("analyze", "analysis").
• Ranking/Score: Numeric relevance (higher = more relevant).

Worked examples

-- Create a searchable index over title and body
CREATE INDEX IF NOT EXISTS idx_articles_fts
ON articles USING GIN (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')));

-- Query: rows containing both "budget" AND "forecast"
SELECT id, title,
       ts_rank(
         to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')),
         to_tsquery('english', 'budget & forecast')
       ) AS rank
FROM articles
WHERE to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')) @@ to_tsquery('english', 'budget & forecast')
ORDER BY rank DESC
LIMIT 10;

-- Create FULLTEXT index
ALTER TABLE tickets ADD FULLTEXT ft_subject_body (subject, body);

-- Phrase search: the exact phrase "credit card"
SELECT id, subject, MATCH(subject, body) AGAINST ('"credit card"' IN BOOLEAN MODE) AS score
FROM tickets
WHERE MATCH(subject, body) AGAINST ('"credit card"' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 10;

-- Require "refund" but exclude "chargeback"
SELECT id, subject
FROM tickets
WHERE MATCH(subject, body) AGAINST ('+refund -chargeback' IN BOOLEAN MODE);

-- Assume a Full-Text Index exists on dbo.KB(title, body)
-- Meaning-based search
SELECT TOP 10 id, title
FROM dbo.KB
WHERE FREETEXT((title, body), 'shipping delay');

-- Exact phrase and prefix search
SELECT TOP 10 id, title
FROM dbo.KB
WHERE CONTAINS((title, body), '"delivery time" OR ship*');

Hands-on exercises

Pick the syntax for your database. If you cannot create indexes, still write the query—focus on correctness.

-- PostgreSQL
CREATE TEMP TABLE products(name text, description text);
INSERT INTO products VALUES
('Alpha Headphones', 'Wireless over-ear headphones with bluetooth 5.0'),
('Bravo Keyboard', 'Wireless compact keyboard with 2.4G receiver'),
('Charlie Mouse', 'Ergonomic bluetooth wireless mouse'),
('Delta Speaker', 'Portable wireless speaker with aux input');
CREATE INDEX ON products USING GIN (to_tsvector('english', coalesce(name,'') || ' ' || coalesce(description,'')));

-- MySQL
CREATE TEMPORARY TABLE products(name varchar(200), description text);
INSERT INTO products VALUES
('Alpha Headphones','Wireless over-ear headphones with bluetooth 5.0'),
('Bravo Keyboard','Wireless compact keyboard with 2.4G receiver'),
('Charlie Mouse','Ergonomic bluetooth wireless mouse'),
('Delta Speaker','Portable wireless speaker with aux input');
ALTER TABLE products ADD FULLTEXT ft_ndx (name, description);

-- SQL Server (temporary example table)
CREATE TABLE #products(name nvarchar(200), description nvarchar(max));
INSERT INTO #products VALUES
('Alpha Headphones', 'Wireless over-ear headphones with bluetooth 5.0'),
('Bravo Keyboard', 'Wireless compact keyboard with 2.4G receiver'),
('Charlie Mouse', 'Ergonomic bluetooth wireless mouse'),
('Delta Speaker', 'Portable wireless speaker with aux input');
-- Assume a Full-Text Index exists on #products(name, description)

-- PostgreSQL
CREATE TEMP TABLE docs(title text, body text);
INSERT INTO docs VALUES
('Intro to ML', 'This article introduces machine learning and basic models.'),
('Deep Learning Tricks', 'Advanced neural networks for image tasks.'),
('Data Cleaning', 'How to clean data before modeling and machine-learning steps.'),
('ML in Production', 'Best practices to deploy machine learning to production.');
CREATE INDEX ON docs USING GIN (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')));

-- MySQL
CREATE TEMPORARY TABLE docs(title varchar(200), body text);
INSERT INTO docs VALUES
('Intro to ML', 'This article introduces machine learning and basic models.'),
('Deep Learning Tricks', 'Advanced neural networks for image tasks.'),
('Data Cleaning', 'How to clean data before modeling and machine-learning steps.'),
('ML in Production', 'Best practices to deploy machine learning to production.');
ALTER TABLE docs ADD FULLTEXT ft_docs (title, body);

-- SQL Server (assume Full-Text Index exists)
CREATE TABLE #docs(title nvarchar(200), body nvarchar(max));
INSERT INTO #docs VALUES
('Intro to ML', 'This article introduces machine learning and basic models.'),
('Deep Learning Tricks', 'Advanced neural networks for image tasks.'),
('Data Cleaning', 'How to clean data before modeling and machine-learning steps.'),
('ML in Production', 'Best practices to deploy machine learning to production.');

Self-check checklist

• I created/used an appropriate full-text index.
• My query uses phrase syntax or boolean operators, not just LIKE.
• I handled nulls safely when concatenating fields (e.g., coalesce).
• I returned ranked results where supported.
• I validated that excluded terms do not appear in results.

Common mistakes and how to self-check

• Relying only on LIKE: It misses word variations and is slow. Self-check: Measure execution time and compare relevance quality.
• No index: Full-text queries without an index can be very slow. Self-check: Confirm the planner uses the FTS index (e.g., EXPLAIN).
• Incorrect language/stopwords: Results seem incomplete. Self-check: Try different configurations or remove very common words from the query.
• Concatenating nullable fields without COALESCE: Can produce null vectors. Self-check: Ensure concatenation handles nulls.
• Over-filtering with NOT: Excluding broad terms may remove relevant rows. Self-check: Inspect a few false negatives and adjust.

Practical projects

• Feedback search: Build a query/report that finds top 10 complaints about "shipping" and ranks them.
• Knowledge base search: Create a stored view that returns ranked results for a term and a phrase, with timestamps.
• Topic trends: Weekly counts of tickets matching "refund" OR "chargeback" minus those containing "fraud".

Learning path

1. Start: Full-text basics (this page).
2. Next: Advanced query syntax (proximity, weights, custom dictionaries).
3. Then: Performance tuning (index types, refresh, maintenance).
4. Finally: Analytics use-cases (topic tagging, cohort building, trend lines).

Next steps

• Complete the exercises above and compare with the solutions.
• Take the Quick Test below to confirm understanding.

Mini challenge

Create a single query that returns the top 5 documents for users searching: must include the phrase "account number", must include either "lost" or "stolen", and must exclude the term "temporary". Return a relevance score and the matched fields.