Searching
By the end of this lesson
Offer text search that performs acceptably as data grows.
Filtering and searching feel similar and behave differently. A filter is exact: departmentId=3 either matches a row or does not. A search is fuzzy: a caller types "men" and expects you to find Menon, possibly also mentions in a job title, ideally with the closest matches first.
That difference decides how the work is done. A filter maps onto an index comparison. A search has to look inside text, and how you do that determines whether the endpoint stays usable as the table grows.
An ordinary database index is a sorted structure. Because the values are in order, the engine can jump to the first entry beginning with "men" and read forward until the prefix stops matching. That is why LIKE 'men%' is fast: the prefix gives it a starting point.
LIKE '%men%' removes the starting point. A match could be at the beginning, the middle or the end of any value, so sorted order tells the engine nothing about where to look. It has to examine every row and test each one. On 5,000 employees that is imperceptible. On five million rows it is a full scan on every keystroke of a type-ahead box.
This is the single most useful thing to understand about search performance, because it explains why the obvious implementation is fine in development and a problem in production, and why the fix is a different kind of index rather than a faster server.
app.MapGet("/api/employees/search", async (
AppDbContext db, CancellationToken ct, string? q = null) =>
{
var term = q?.Trim() ?? "";
if (term.Length < 2)
{
return Results.ValidationProblem(new Dictionary<string, string[]>
{
["q"] = ["Enter at least two characters to search for."]
});
}
var matches = await db.Employees
.Where(e => e.Surname.StartsWith(term) || e.Email.StartsWith(term))
.OrderBy(e => e.Surname)
.ThenBy(e => e.Id)
.Take(20)
.Select(e => new EmployeeListItem(e.Id, e.FullName, e.Department!.Name))
.ToListAsync(ct);
return Results.Ok(new { items = matches, count = matches.Count });
});- StartsWith translates to LIKE 'term%', which an index on Surname can seek into. Contains would translate to LIKE '%term%', which cannot.
- The minimum length is a real protection, not politeness. A single character matches a large fraction of the table, so every such request does close to the maximum amount of work for a result nobody can use.
- Take(20) bounds the response and the work behind it. A search endpoint with no limit is a way to ask for the whole table using a very short query string.
- Two columns are searched, both indexed. Adding a third is a decision about an index, not only about the code — searching every text column is how a search endpoint becomes the slowest thing in the API.
- The ordering is by surname rather than by relevance, because this query has no notion of relevance. Being honest about that matters: if callers need the best match first, prefix matching is not the tool.
A progression that avoids both of the usual errors — shipping a full scan, and installing a search cluster for 30,000 rows:
Bound the request first
A minimum term length, a maximum result count, and a defined set of columns to search. These cost nothing and remove the worst behaviour regardless of what you do next.
Start with prefix matching on indexed columns
For names, codes, emails and references this is often all anyone needs, and it uses the indexes you already have. Type-ahead on a surname is a prefix problem.
Move to your database's full-text search when substrings matter
SQL Server, PostgreSQL, MySQL and SQLite all have one. They build an index of the words inside the text, so matching a word in the middle of a description becomes a seek rather than a scan, and they add stemming so "manage" finds "managing".
Measure with production-sized data
Copy or generate a realistic row count and record how long the search takes at the 95th percentile. A query plan showing a scan of a million rows is the evidence that decides the next step; a hunch is not.
Consider a dedicated search index only when the evidence supports it
Relevance ranking, typo tolerance, faceted counts and search across several entity types at once are the features databases do not offer well. Those needs, plus measured pain, justify the extra system.
What a dedicated search index gives you, and what it asks in return:
- Gives: relevance ranking
- Results ordered by how well they match, weighted across fields — a surname match scoring above a mention in a description.
- Gives: tolerance and analysis
- Typos, plurals, stemming, synonyms and accent folding, so "Menoon" finds Menon and "orders" finds order.
- Gives: facets
- Counts per department or status alongside the results, which are expensive to compute as separate database queries.
- Costs: a second copy of the data
- Your records have to be pushed into the index and kept up to date. That is a pipeline to build, monitor and re-run when it falls behind.
- Costs: eventual consistency
- A newly created employee is in the database before it is in the index. Callers see a delay between writing and finding, and your API has to be honest about that.
- Costs: another system to operate
- Provisioning, upgrades, backups, capacity and a new failure mode to handle when the index is unavailable and the rest of the API is not.
Summary
- A filter matches exactly; a search looks inside text, and that is a different index problem
- A prefix match seeks within a sorted index; a leading wildcard removes the starting point, so every row is examined
- Bound every search: minimum term length, a result limit, and a fixed set of columns
- Use the database's full-text search at moderate scale, and a dedicated index when relevance, tolerance or facets are genuinely required
- A separate search index adds a data pipeline, a consistency delay and another system to run
Practice
Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.
Think about it
Why the wildcard changes everything
Explain, in terms of how a sorted index works, why LIKE 'men%' can use an index on Surname but LIKE '%men%' cannot.
Then say what that means for a type-ahead box over a table of five million rows.
Show solution
An index keeps values in sorted order. A known prefix identifies a contiguous range within that order, so the engine seeks to the first entry starting with "men" and reads forward until the prefix no longer matches. It never looks at the rest.
A leading wildcard means a match can occur anywhere inside a value. Sorted order gives no clue where such values sit, so there is no range to seek to and every row must be tested.
For a type-ahead over five million rows, that is a full scan per keystroke, multiplied by every user typing at once. The fix is an index built for the job — the database's full-text index, which indexes the words inside the text — or restricting the feature to prefix matching, which many search boxes over names can accept.
Try it yourself
Measure before you choose
Create a table with at least 200,000 rows of realistic text. Time a prefix search and a substring search on the same column, with and without an index.
Record four timings and look at the query plan for each.
Show solution
The prefix search with an index will show a seek and stay roughly flat as the table grows. The substring search will show a scan, and its time will track the row count regardless of the index.
The unindexed prefix search is the instructive fourth case: it scans too, which shows that the index and the query shape have to match. Neither one alone is what makes a search fast.
Having your own four numbers changes the conversation about search infrastructure, because it replaces an argument about what ought to be fast with evidence about what is.
Saved in this browser only.