SQL Full-Text Search: Beyond LIKE for Text Queries
LIKE with wildcards is slow and imprecise. Full-text search is the right tool for searching text. Here is how I set it up and query it. Text search in SQL often starts with LIKE. Developers write WHERE title LIKE '%keyword%' and it works for a few hundred rows. As the table grows, LIKE becomes slow because it cannot use an index when the wildcard is at the start. It also matches substrings poorly, finding "cat" inside "category". Full-text search solves both problems. It indexes words, not substrings, and it ranks results by relevance. Here is how I set up and query full-text search in SQL. Why LIKE Fails LIKE with a leading wildcard like %keyword forces a sequential scan. The database must check every row because no index can serve a pattern that can match anywhere in the string. LIKE also matches substrings, so searching for "art" matches "start" and "cart". For user-facing search, these false positives are unacceptable. . Slow: sequential scan, matches substrings SELECT * FROM articles WHERE body LIKE '%sql%'; . Also slow, also matches substrings SELECT * FROM articles WHERE body LIKE '%sql%' OR body LIKE '%database%'; Full-text search breaks text into…