LIKE '%word%' is not how you search a billion messages

A leading-wildcard LIKE scan reads the table. Full-text search uses an inverted index: word → message IDs. Intersect the lists. Skip the common words.

LIKE '%word%' is not how you search a billion messages

The question: a billion short messages in SQL. Find every message that contains all words from a list.

The junior answer is honest and wrong at this scale:

WHERE body LIKE '%invoice%'
  AND body LIKE '%failed%'

%word% cannot use a normal B-tree the way a prefix search can. The database is walking rows. A billion rows later, the query is still walking.

Search the index, not the messages

Full-text search uses an inverted index:

failed   → [id, id, id, ...]
invoice  → [id, id, ...]

To find messages that contain every word, you intersect those ID lists.

That is why it is fast: you never read a billion bodies. You merge posting lists.

In PostgreSQL that is typically to_tsvector plus a GIN index, and to_tsquery / @@ on the query side. In MySQL it is FULLTEXT and MATCH ... AGAINST.

I have used this on product names and descriptions. The difference versus LIKE is not a tuning trick. It is a different data structure.

The trap: common words

Intersecting a list for "the" with a list for "is" is huge. Those words appear almost everywhere. They make the intersection slow and they add no meaning.

Start with the rarest term. Drop stopwords. If the query is ten words of filler, the index cannot save a bad query.

When the corpus and the query patterns outgrow what the database full-text engine is for, that is when people reach for a search engine. Not before you have an inverted index in the database.

Takeaway

LIKE '%…%' is a scan with extra steps.

Word search at scale is: token → IDs, then intersect.

If the query is a bag of common words, fix the query. The index will not invent relevance for you.