Why Your Database Falls Short for Search
LIKE queries, ILIKE, and why they break at scale
Written by the RadarTrek editorial team · June 2026
The temptation of LIKE
Most apps start search the same way: a `WHERE title ILIKE '%query%'` clause tacked onto an existing query. It works. It ships. Then the table grows to 500k rows, the UI response time climbs past two seconds, and users start noticing.
The filing-cabinet analogy
A database index is like a book's index at the back. When you search for "encryption" you jump straight to page 312. But a LIKE `%query%` search is like reading every page of the book from cover to cover — because the wildcard at the start means the index cannot be used at all.
What LIKE cannot do
- Leading wildcards (`%query`) disable all B-tree indexes
- Case-insensitive ILIKE forces a sequential table scan
- Typo tolerance is zero — "receiv" matches nothing
- Relevance ranking requires a separate ORDER BY guess
- Stemming is absent — "running" does not match "run"
A concrete performance cliff
products table — 600k rows
-- This query does a sequential scan on every row EXPLAIN ANALYZE SELECT id, name, description FROM products WHERE name ILIKE '%wireless headphones%' ORDER BY created_at DESC LIMIT 20; -- Seq Scan on products (cost=0.00..28432.00 rows=3 width=120) -- (actual time=1823.411..1823.602 rows=3 loops=1) -- Planning Time: 0.4 ms -- Execution Time: 1824.1 ms ← 1.8 seconds for 3 results
When LIKE is fine
Small tables (under 10k rows), admin panels that tolerate latency, and exact-prefix searches (`query%` without a leading wildcard) can all use LIKE acceptably. Do not over-engineer. This course is for when LIKE is no longer enough.
Quick check
Run `EXPLAIN ANALYZE` on your existing search query. If you see "Seq Scan" and execution time above 100ms, it is time to upgrade your search strategy.
Key ideas from this lesson