Database.Advanced.How would you implement full-text search in SQL?

To implement full-text search (FTS) in SQL, you need to go beyond simple LIKE queries and use built-in text indexing and search functions that support:

• Tokenization (splitting into words)
• Ranking / relevance
• Stemming / stop words
• Boolean logic (AND, OR, NOT)

Here’s how to do it in popular databases:

---

🧰 1. PostgreSQL: Best-in-Class Native FTS

✅ Step-by-step:

-- Create a table
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  body TEXT,
  tsv tsvector -- preprocessed searchable vector
);

-- Populate the tsvector column
UPDATE articles SET tsv = 
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));

-- Add an index
CREATE INDEX idx_articles_fts ON articles USING GIN(tsv);

-- Search
SELECT id, title
FROM articles
WHERE tsv @@ to_tsquery('english', 'search & term');

🔍 Explanation:

• to_tsvector = tokenize and normalize text
• to_tsquery = parse a query string ('cat & dog', 'run | jump')
• GIN index = fast inverted index for full-text search

🚀 Bonus: Auto-update tsv using a trigger

CREATE TRIGGER tsv_update BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);

🧰 2. MySQL (InnoDB since 5.6)

✅ Step-by-step:

-- Create table with full-text index
CREATE TABLE articles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title TEXT,
  body TEXT,
  FULLTEXT(title, body)
);

-- Search using MATCH ... AGAINST
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('+search +term' IN BOOLEAN MODE);

🔍 Notes:

• Use IN BOOLEAN MODE for advanced searches (+must, -exclude, *wildcards)
• Relevance ranking is returned with MATCH(...) AGAINST(...) AS score
• Works best on InnoDB with proper indexing

🧰 3. SQLite

Since v3.9, SQLite has FTS5 extension:

-- Create a virtual table
CREATE VIRTUAL TABLE articles USING fts5(title, body);

-- Insert data
INSERT INTO articles (title, body) VALUES ('PostgreSQL Guide', 'Learn full-text search');

-- Search
SELECT * FROM articles WHERE articles MATCH 'full +text';

🔍 When NOT to use Full-Text in SQL

For complex needs (e.g., fuzzy search, typo tolerance, multi-language stemming), consider:

• 🟡 Elasticsearch
• 🟡 Apache Solr
• 🟡 Meilisearch

These are search engines designed for more advanced full-text capabilities.

✅ Summary

Database	FTS Support	Notes
PostgreSQL	✅ Excellent	tsvector, tsquery, GIN indexes
MySQL	✅ Good	MATCH ... AGAINST, limited stemming
SQLite	✅ Light	FTS5 extension
SQL Server	✅ Yes	Full-text catalog & CONTAINS/ FREETEXT