Full-Text Search
debt(d7/e5/b5/t7)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints list mysql-explain and laravel-scout as tools. mysql-explain can reveal full table scans but requires the developer to actively run EXPLAIN on suspect queries — it won't proactively flag LIKE '%term%' patterns at write time. laravel-scout guides toward search abstraction but doesn't auto-detect missing FULLTEXT indexes. The code_pattern is identifiable only if someone is auditing queries or profiling under load, making this effectively a review/runtime discovery rather than an automatic catch.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix acknowledges two tiers: adding a MySQL FULLTEXT index with MATCH AGAINST is a moderate change (schema migration plus query rewrites), and migrating to Meilisearch or Elasticsearch is a more significant effort involving new infrastructure, indexing pipelines, and query-layer changes. Even the simpler fix touches schema, queries, and potentially model/repository layers across a component, placing this solidly at e5.
Closest to 'persistent productivity tax' (b5). The choice applies to web and API contexts broadly. Using LIKE '%term%' as a de-facto search strategy means every search-related feature, query optimization review, and scaling discussion is haunted by the absent index. It slows down multiple work streams (performance tuning, feature additions involving search) but doesn't fully define the system's shape — a b5 fits because it's a recurring tax across search-related work without being fully load-bearing across the whole codebase.
Closest to 'serious trap — contradicts how a similar concept works elsewhere' (t7). The misconception is explicit: developers assume LIKE '%keyword%' is equivalent to full-text search for most use cases. This is a deeply intuitive but completely wrong belief — LIKE with a leading wildcard looks syntactically similar to a search operation but silently performs a full table scan, contrary to how indexed lookups behave. The behavior contradicts reasonable developer expectations about SQL index usage, making it a serious cognitive trap just short of catastrophic.
Also Known As
TL;DR
Explanation
SQL LIKE '%query%' cannot use a B-tree index and requires a full table scan. Full-text search uses inverted indexes — a mapping from words to the documents containing them — enabling relevance-ranked search in milliseconds. MySQL FULLTEXT indexes (InnoDB, Boolean and natural language modes) and PostgreSQL's tsvector/tsquery support basic full-text search. Elasticsearch and OpenSearch provide production-grade full-text search with fuzzy matching, faceting, autocomplete, and multi-field relevance tuning. In PHP, integration via the elasticsearch-php client or Laravel Scout abstracts the provider.
Common Misconception
Why It Matters
Common Mistakes
- Using LIKE '%term%' for search — always a full table scan, unusable at scale.
- Not using MySQL FULLTEXT or PostgreSQL tsvector indexes for text search fields.
- Expecting full-text search to handle fuzzy matching — use a dedicated search engine (Elasticsearch, Meilisearch) for that.
- Not stemming or normalizing search terms — 'running' and 'run' match different documents without stemming.
Code Examples
-- Full table scan on every search:
SELECT * FROM articles WHERE content LIKE '%php security%'; -- Scans all rows
-- MySQL FULLTEXT index:
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, content);
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('php security' IN BOOLEAN MODE);-- MySQL FULLTEXT — faster than LIKE '%keyword%'
-- Create FULLTEXT index
ALTER TABLE articles ADD FULLTEXT INDEX ft_body(title, body);
-- Natural language search
SELECT *, MATCH(title,body) AGAINST('php generators' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title,body) AGAINST('php generators' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;
-- PostgreSQL tsvector (more powerful)
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);
SELECT * FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'php generators')
ORDER BY ts_rank(search_vector, plainto_tsquery('english', 'php generators')) DESC;