Bits of .NET

🔍 LIKE ‘%word%’ is Slow

LIKE with leading wildcard can’t use indexes. Full-Text Search indexes words. Search millions of rows in milliseconds.

📝 Create Full-Text Index

-- PostgreSQL
CREATE INDEX idx_posts_search ON posts USING GIN (to_tsvector('english', title || ' ' || content));

-- MySQL
ALTER TABLE posts ADD FULLTEXT INDEX idx_search (title, content);

-- SQL Server
CREATE FULLTEXT CATALOG ft_catalog AS DEFAULT;
CREATE FULLTEXT INDEX ON posts(title, content) KEY INDEX pk_posts;

🎯 Search Queries

-- PostgreSQL
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'database & performance');

-- MySQL
SELECT * FROM posts
WHERE MATCH(title, content) AGAINST('database performance' IN NATURAL LANGUAGE MODE);

-- SQL Server
SELECT * FROM posts
WHERE CONTAINS((title, content), 'database AND performance');

-- Rank by relevance
SELECT *, MATCH(title, content) AGAINST('database') as relevance
FROM posts
WHERE MATCH(title, content) AGAINST('database')
ORDER BY relevance DESC;

💡 LIKE vs Full-Text

• LIKE: 10 seconds on 1M rows (scan)
• Full-Text: 50ms on 1M rows (index)
• Full-Text supports: stemming, stop words, ranking
• Full-Text supports: Boolean operators (AND, OR, NOT)
• Not for exact string matching (use equality for that)