Building Search: From Database LIKE to Dedicated Search Engines
Decide when a plain database query (LIKE, MySQL FULLTEXT, or Postgres tsvector/tsquery) is sufficient for search versus when a dedicated engine like Elasticsearch or OpenSearch is warranted, understand the core concepts (inverted indexes, analyzers, relevance scoring) that make those engines different, design an indexing strategy, and keep a search index synchronized with the source-of-truth database.
Almost every application eventually needs to let users search for something — products, articles, users, support tickets — and the temptation is to reach immediately for a dedicated search engine because that's what large, well-known products use. In reality, a plain SQL query is enough for a large share of real-world search features, and reaching for Elasticsearch or OpenSearch before you actually need one adds an entire second data store, an entire second synchronization problem, and real operational overhead. This lesson works through the decision honestly: what a database can do for search on its own, what it fundamentally cannot do well, what a dedicated search engine is actually built to solve, how to design what gets indexed, and — the part most tutorials skip — how you keep that separate index from silently drifting out of sync with the database that remains your actual source of truth.
When Plain Database Search Is Genuinely Enough
A LIKE '%term%' query against a text column is the simplest form of search PHP applications reach for, and for small tables (thousands, not millions, of rows) with modest traffic, it is often completely adequate — it requires no new infrastructure, has no synchronization problem because it queries the live table directly, and is trivial to reason about. Its weaknesses are equally clear: a leading-wildcard LIKE '%term%' cannot use a standard B-tree index at all, meaning it performs a full table scan every single time, which becomes painfully slow as a table grows; it also has no concept of relevance (every match is equally 'good'), no stemming (searching 'running' won't match rows containing 'run'), and no tolerance for typos.
The next step up, still entirely inside the database, is a proper full-text index. MySQL and MariaDB support FULLTEXT indexes on text columns, queried with MATCH(...) AGAINST(...), which use an inverted-index-like structure internally, support natural-language relevance ranking, and are dramatically faster than LIKE for keyword search at moderate scale. PostgreSQL's equivalent is more powerful still: a tsvector column (a preprocessed, normalized representation of searchable text — lowercased, stemmed, stripped of stop words) combined with tsquery for the search term, indexed with a GIN index, supports ranking (ts_rank), phrase search, and language-aware stemming, all as ordinary SQL.
These database-native approaches are enough when: the dataset is small-to-medium (roughly up to a few million rows, though this varies a lot by hardware and query pattern), search is a secondary feature rather than the core product experience, you don't need faceted filtering combined with full-text relevance in one query, and you don't need near-instant results across many different content types at once. A huge number of successful applications — most admin panels, many e-commerce sites, most internal tools — never outgrow this tier, and adding a dedicated search engine to them would be pure unnecessary complexity.
• LIKE '%term%' — simplest option, but a leading wildcard defeats indexes and forces a full table scan; no relevance ranking. • MySQL/MariaDB FULLTEXT + MATCH() AGAINST() — real inverted-index-like search inside the database, with natural-language ranking. • PostgreSQL tsvector/tsquery + GIN index + ts_rank — the most capable in-database option: stemming, stop words, phrase search, ranking. • Enough for small-to-medium datasets where search is a secondary feature, not the core product experience.
When You Need a Dedicated Search Engine
The signal that it's time to reach for Elasticsearch, OpenSearch, or a similar dedicated search engine is usually a combination of scale and quality expectations that a database's full-text features weren't designed for: millions to billions of documents, sub-100-millisecond search latency expectations even under heavy concurrent load, typo tolerance ('fuzzy' matching), relevance that needs to combine many signals (text match quality, recency, popularity, business rules) with tunable weighting, faceted search (filter counts across multiple dimensions updating live as the user narrows results), autocomplete-as-you-type, and search across heterogeneous, denormalized data that would otherwise require expensive joins in the database on every query.
Running that kind of search against your primary transactional database is also a reliability risk independent of whether the database can technically execute the query: expensive full-text queries compete for the same connections, CPU, and I/O that your application's core read/write traffic depends on, so a spike in search traffic (or one particularly broad query) can degrade the site's ordinary functionality. A dedicated search engine is architecturally separate specifically so a search-traffic spike or an expensive query stays contained to a system whose only job is search, and does not threaten checkout, login, or any other core path.
The tradeoff is real, though: adding a search engine means running (or paying for a managed) additional service, learning a genuinely different query language and data model, and — the hardest part in practice — solving the synchronization problem covered later in this lesson. Teams that adopt Elasticsearch prematurely, for a dataset of a few thousand rows with no relevance or scale requirements, often end up maintaining meaningfully more operational complexity than the search feature itself justifies.
• Signals it's time: millions+ of documents, strict latency requirements, typo tolerance, tunable multi-factor relevance, live facets, autocomplete. • A dedicated engine isolates expensive search load from your primary transactional database, protecting core app functionality from search-traffic spikes. • The cost: another service to run/pay for, a new query language to learn, and an ongoing index-synchronization problem to solve correctly. • Don't adopt a dedicated search engine before the dataset size or feature requirements actually demand it — the operational cost is real.
Inverted Indexes, Analyzers, and Tokenization
The core data structure behind virtually every full-text search system — database FULLTEXT indexes and dedicated engines alike — is the inverted index. Instead of storing, for each document, the words it contains (a 'forward' index, which is what you'd naturally build first), an inverted index stores, for each unique word (or 'term'), the list of documents that contain it. Searching for a term then becomes a direct lookup ('which documents contain word X?') rather than scanning every document's text — this is what makes full-text search fast at scale, the same way a book's index at the back lets you jump straight to the relevant pages instead of reading the whole book.
Before a document's text is inserted into that index, it passes through an analyzer, which itself is a pipeline of steps: a tokenizer breaks raw text into individual terms (splitting on whitespace and punctuation, generally), and then a series of token filters transform those terms — lowercasing everything so 'PHP' and 'php' match the same term, removing common 'stop words' (the, is, at) that carry little search value, and stemming words down to a root form (so 'running', 'runs', and 'ran' can all be indexed and matched as some form of 'run'). The exact same analyzer must be applied both when indexing a document and when parsing a user's search query, or terms will never line up correctly between what was indexed and what's being searched for.
This is also where language-specific and domain-specific customization happens: an English analyzer's stemming rules are wrong for German or Japanese text, and a product-catalog search might want a custom analyzer that treats model numbers or SKUs as atomic tokens rather than splitting them on hyphens. Elasticsearch/OpenSearch expose deep configuration of analyzers per field, which is one of the main reasons they outperform a database's fixed, much less configurable full-text behavior for serious search products.
• Inverted index: for each term, a list of the documents containing it — enables direct lookup instead of scanning every document. • Analyzer pipeline: tokenizer (splits text into terms) + filters (lowercasing, stop-word removal, stemming). • The same analyzer must run at both index time and query time, or indexed terms and search terms won't match. • Dedicated engines allow deep, per-field, per-language analyzer customization — a major reason they outperform database full-text search for serious use cases.
Relevance Scoring
Once multiple documents match a query, a search system has to decide what order to return them in, and that ordering — relevance — is itself a nontrivial computation, not a simple boolean match. The classical algorithm behind most full-text relevance scoring, used by both database full-text engines and Elasticsearch/OpenSearch by default (as BM25, a refinement of the older TF-IDF), scores a document higher when a query term appears more frequently within that specific document (term frequency) but discounts terms that appear across almost every document in the collection anyway (inverse document frequency) — so a rare, specific term matching is weighted more heavily than a common word matching, and it also normalizes for document length so a short document isn't unfairly penalized or a long one unfairly favored purely due to size.
Dedicated search engines let you go well beyond that default text-relevance score by combining it with other signals through boosting and scoring functions: boosting a match in a title field more than the same match in a body field, factoring in recency (a newer article ranks slightly higher, all else equal), factoring in business signals like popularity or purchase count, and combining several of these into one final score with configurable weights. This kind of multi-signal relevance tuning is realistically only practical in a dedicated engine — replicating it as raw SQL ordering logic becomes unwieldy very quickly.
It's worth being honest that relevance tuning is an ongoing, iterative product concern, not a one-time setup step: teams that take search seriously continuously observe what users click on versus what they search for, and adjust field boosts and scoring functions over time based on that behavior — 'good search' is a moving target, not a fixed configuration.
• BM25 (a refinement of TF-IDF) is the default relevance algorithm in most full-text systems: rewards term frequency within a document, discounts terms common across the whole collection, normalizes for document length. • Dedicated engines let you combine text relevance with other signals: field boosting (title > body), recency, popularity — a full custom scoring function. • Multi-signal relevance tuning is realistically impractical to replicate purely in SQL ORDER BY logic at any meaningful scale. • Relevance tuning is ongoing product work — observing click behavior and adjusting boosts over time, not a one-time configuration.
Indexing Strategy: What to Index and How to Denormalize
Deciding what goes into a search index is a distinct design exercise from your database's normalized schema, and the two should generally look different on purpose. A search index document is typically denormalized: instead of a products row that references a separate categories table by foreign key, the indexed document for a product embeds the category name directly (and maybe the brand name, and maybe a few key attribute values) so that a single search query against the index can filter and facet on category or brand without any join at all — search engines are not built to do relational joins efficiently, and fighting that by trying to normalize your index defeats the reason to use one.
The fields you choose to index should map directly to how users will actually search and filter: text fields that need full-text matching (title, description, tags) get analyzed text mappings; fields used for exact filtering or faceting (category, brand, in-stock status, price range) get separate 'keyword'/exact-match mappings or numeric mappings rather than being run through a text analyzer; and fields used purely for sorting (price, rating, created date) need their own appropriately-typed mapping as well. It's common, and generally correct, for a single source field to be indexed twice under different mappings — once analyzed for search, once as an unanalyzed keyword for exact filtering and aggregation — because a single field type can't serve both purposes well.
A useful discipline is to only index what search and filtering actually need, not simply mirror the entire database row. Every additional field indexed adds index size, indexing time, and one more thing to keep synchronized; a search index that duplicates dozens of rarely-searched columns 'just in case' accumulates cost without corresponding benefit. Fields the user interface needs to display in search results but never searches or filters by can sometimes be fetched back from the primary database by ID after the search engine returns matching IDs, rather than being duplicated into the index at all — the right tradeoff depends on how much that extra database round-trip costs versus the extra index size.
• Search index documents are intentionally denormalized — embed related data (category name, brand) directly rather than requiring joins the search engine can't do efficiently. • Map fields to their actual use: analyzed text for full-text search, keyword/exact mappings for filtering and facets, typed fields for sorting. • The same source field is often indexed twice under different mappings — one analyzed, one exact — because one type can't serve both search and faceting well. • Index only what search/filtering actually needs; consider fetching purely-display fields back from the database by ID instead of duplicating everything into the index.
Keeping the Index in Sync with the Source of Truth
The moment you introduce a separate search index, you have created a second copy of some of your data, and that copy will drift out of sync with the database unless something actively keeps it updated — this is, in practice, the hardest and most underestimated part of adopting a dedicated search engine. There are two broad strategies, and most serious systems end up using a hybrid of both.
Synchronous indexing updates the search index inline, in the same request that wrote to the database — after a product is saved, the request handler also calls the search engine's API to index (or re-index) that one document before responding. This keeps the index maximally fresh (searchable within the same request that made the change) but couples your database write path's latency and reliability to the search engine's availability: if the search engine is briefly slow or unreachable, either the whole request slows down waiting for it, or you need explicit error-handling to decide the write should still succeed even if indexing fails (and then you have a document that needs to be reindexed later — drift again).
Asynchronous reindexing decouples the two: after a database write, the application pushes a small message ('reindex product #4821') onto a queue, and a separate worker process consumes that queue and performs the actual indexing call against the search engine, independent of the original request's timing. This means a slow or temporarily-down search engine never affects the responsiveness of the core application, and a burst of writes gets smoothed out by the queue rather than hammering the search engine simultaneously — at the cost of a small, usually sub-second-to-a-few-seconds delay before new/changed data becomes searchable, which is an acceptable tradeoff for the overwhelming majority of search features.
Because any asynchronous system can still drop a message or fail a job, mature indexing pipelines also run a periodic full (or incremental, based on an updated_at timestamp) reindex job as a safety net — the same reconciliation instinct used for payments applies here: don't just trust the queue-based updates to always land, periodically re-derive the index from the database and correct any drift you find.
• A separate search index is a second copy of your data — it will drift unless something actively keeps it synchronized. • Synchronous indexing (inline with the database write): freshest results, but couples request latency/reliability to the search engine's availability. • Asynchronous reindexing (via a queue and a worker): decouples the app from search-engine hiccups, at the cost of a short indexing delay. • Run a periodic full or incremental reconciliation reindex as a safety net against dropped messages or failed jobs, the same way payments reconciliation catches missed webhooks.
Indexing and Querying with the Elasticsearch/OpenSearch HTTP API
Elasticsearch/OpenSearch expose a plain HTTP/JSON API — no special PHP extension is required, an HTTP client is enough. The document ID ({$productId}) should match your database's primary key so results can be correlated back to source rows.
<?php
declare(strict_types=1);
$client = new SearchHttpClient('http://localhost:9200');
// Index (create or replace) one document
$client->put("/products/_doc/{$productId}", [
'title' => $title,
'description' => $description,
'category' => $categoryName, // denormalized, no join needed
'price' => $priceCents,
]);
// Query
$results = $client->post('/products/_search', [
'query' => ['match' => ['title' => $searchTerm]],
]);Code Examples
The indexed document is deliberately denormalized (category and brand names embedded directly, not foreign keys) so a search query never needs to join back to the relational database, and using the product's own primary key as the document ID makes later updates ('PUT the same ID again') simple overwrites rather than requiring a separate lookup step.
CURLOPT_CUSTOMREQUEST => 'PUT', // PUTting the same document ID again replaces it
The 'must' clause performs relevance-scored full-text matching against the analyzed title field, while the 'filter' clauses do exact, unscored matching on category and a numeric range on price — separating 'what should affect ranking' from 'what should just narrow the result set' is a fundamental and frequently used pattern in dedicated search engines. filter clauses are also typically faster than must/match clauses because the engine can cache them, since they don't need to compute a relevance score.
<?php
declare(strict_types=1);
function indexProduct(int $productId, array $product): void
{
$document = [
'title' => $product['title'],
'description' => $product['description'],
'category' => $product['category_name'], // denormalized from a join
'brand' => $product['brand_name'],
'price_cents' => $product['price_cents'],
'in_stock' => $product['stock_count'] > 0,
'updated_at' => $product['updated_at'],
];
$ch = curl_init("http://localhost:9200/products/_doc/{$productId}");
curl_setopt_array($ch, [
CURLOPT_CUSTOMREQUEST => 'PUT',
CURLOPT_RETURNTRANSFER => true,
CURLOPT_HTTPHEADER => ['Content-Type: application/json'],
CURLOPT_POSTFIELDS => json_encode($document, JSON_THROW_ON_ERROR),
]);
$response = curl_exec($ch);
$status = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($status >= 300) {
throw new RuntimeException("Failed to index product {$productId}: {$response}");
}
}
// After a database write, enqueue this instead of calling it inline, so a
// slow search engine never blocks the checkout/save request:
// $queue->push('rein
…<?php
declare(strict_types=1);
function searchProducts(string $term, string $category, int $maxPriceCents): array
{
$query = [
'query' => [
'bool' => [
'must' => [
['match' => ['title' => $term]], // full-text, analyzed, scored
],
'filter' => [
['term' => ['category' => $category]], // exact match, no scoring
['range' => ['price_cents' => ['lte' => $maxPriceCents]]],
],
],
],
'size' => 20,
];
$ch = curl_init('http://localhost:9200/products/_search');
curl_setopt_array($ch, [
CURLOPT_POST => true,
CURLOPT_RETURNTRANSFER => true,
CURLOPT_HTTPHEADER => ['Content-Type: application/json'],
CURLOPT_POSTFIELDS => json_encode($query, JSON_THROW_ON_ERROR),
]);
$response = json_decode(curl_exec($ch), true, flags: JSON_THROW_ON_ERROR);
curl_close($ch);
return array_map(
fn(array $hit) => $hit['_source'] + ['_score' => $hit['_score']],
$response['hits']['hits']
);
}
$results = searchProducts('wireless keyboard', 'electronics', 5
…Keeping a Search Index Synchronized
Async reindexing keeps the app responsive; a periodic reconciliation job catches whatever the queue missed.
Database write Queue Search worker
┌───────────────┐ enqueue ┌─────────┐ consume ┌──────────────┐
│ UPDATE products│ ────────▶│ reindex │ ────────▶│ index into │
│ SET ... │ │ product │ │ Elasticsearch │
└───────────────┘ │ #4821 │ └──────────────┘
└─────────┘
nightly job: diff DB vs index, backfill/correct driftImportant Rules
Start with database-native search (FULLTEXT, tsvector) — only adopt a dedicated search engine when scale or relevance requirements genuinely demand it.
Apply the exact same analyzer at index time and query time, or terms will silently fail to match.
Denormalize data into the search index deliberately — search engines are not built for relational joins.
Prefer asynchronous reindexing via a queue over synchronous inline updates, to avoid coupling request latency to search-engine availability.
Run periodic reconciliation reindexing as a safety net, even with reliable queue-based updates in place.
Denormalization in E-Commerce Search
A large online marketplace's product search index typically embeds seller name, category path, and current price directly into each indexed document, refreshed via an asynchronous pipeline, specifically so that a single search query can filter, facet, and sort across millions of listings without a single join back to the relational catalog database during the request.
SELECT * FROM products WHERE description LIKE '%wireless keyboard%';
SELECT * FROM products WHERE MATCH(description) AGAINST('wireless keyboard' IN NATURAL LANGUAGE MODE); -- or migrate to a dedicated engine once scale/relevance needs exceed this
A leading-wildcard LIKE query cannot use a standard index and forces a full table scan on every search, which degrades badly as the table grows; a FULLTEXT index (or a dedicated search engine beyond that) is built specifically for this access pattern.
// Index a product inline during the checkout/save request function saveProduct($data) { db_save($data); indexProduct($data); } function saveProduct($data) { db_save($data); $queue->push('reindex_product', ['id' => $data['id']]); }
Calling the search engine synchronously inside a core write path couples that request's latency and success to the search engine's availability; queuing the reindex keeps the application responsive even if the search engine is slow or briefly down.
Why does a leading-wildcard LIKE '%term%' query perform poorly on a large table? A. LIKE queries are disabled by default in most databases B. A leading wildcard prevents the database from using a standard index, forcing a full table scan C. LIKE only works on numeric columns D. It only searches the first row of the table
B. A leading wildcard prevents the database from using a standard index, forcing a full table scan — A standard B-tree index can't be used to satisfy a pattern that starts with a wildcard, so the database must scan every row to check for a match, which scales poorly.
What is an inverted index, and why does it make full-text search fast? A. A copy of the database sorted in reverse order B. A structure mapping each term to the list of documents containing it, enabling direct lookup instead of scanning every document C. An index that only works on integer primary keys D. A backup index used only when the primary index fails
B. A structure mapping each term to the list of documents containing it, enabling direct lookup instead of scanning every document — By storing, for each term, which documents contain it, a search for that term becomes a direct lookup rather than a scan of every document's full text — the same principle as a book's back-of-book index.
Why must the same analyzer be applied at both index time and query time? A. It isn't required — analyzers only affect index size, not matching B. If indexing and querying use different tokenization/stemming rules, indexed terms and search terms won't line up and matches will silently fail C. Using different analyzers doubles the storage cost D. Analyzers are only relevant for numeric fields
B. If indexing and querying use different tokenization/stemming rules, indexed terms and search terms won't line up and matches will silently fail — An analyzer transforms raw text into the terms actually stored/searched (lowercasing, stemming, stop-word removal); mismatched analyzers between indexing and querying mean the terms being compared were normalized differently and may never match.
What is the main tradeoff of asynchronous reindexing via a queue, compared to synchronous inline indexing? A. Asynchronous reindexing is always less accurate and should be avoided B. It introduces a short delay before new/changed data becomes searchable, in exchange for decoupling request latency from the search engine's availability C. It requires abandoning relevance scoring entirely D. It only works with MySQL, not PostgreSQL
B. It introduces a short delay before new/changed data becomes searchable, in exchange for decoupling request latency from the search engine's availability — Queuing reindex work means a slow or temporarily unavailable search engine doesn't slow down or fail the original database-writing request, at the cost of new data being searchable slightly later rather than instantly.
Write a PHP function that builds an Elasticsearch/OpenSearch query body (as a PHP array, ready for json_encode) that full-text matches a search term against a 'title' field and filters results to only 'in_stock' => true documents, sized to 10 results.
<?php function buildInStockSearchQuery(string $term): array { return [ 'query' => [ 'bool' => [ 'must' => [ ['match' => ['title' => $term]], ], 'filter' => [ ['term' => ['in_stock' => true]], ], ], ], 'size' => 10, ]; }
Use a 'bool' query with a 'must' clause containing a 'match' on title, and a 'filter' clause containing a 'term' on in_stock.