Why Your App's Search Box Might Be Quietly Failing

Almost every brief I get includes "search" somewhere on the feature list. It's usually one line, tucked in between "user roles" and "export to CSV". Nobody thinks about it much because everyone has used Google. How hard can a search box be?

Then the app goes live, someone searches for a customer called "McDonald" and gets nothing because it's stored as "Mcdonald", or they search for an order by the last four digits of a reference number and the search box returns nothing because it only matches from the start of a word. That's when I get the call.

What "add search" usually means in practice

Most search boxes start life as a single line of code: find any row where a column contains the typed text. In Laravel that's a WHERE column LIKE '%term%' clause, or something similar in raw SQL. It works fine in a demo with twenty rows of test data. It's also genuinely fine for plenty of small internal tools where the table stays small and the search only needs to match one field.

The trouble starts when any of these become true: the table has tens of thousands of rows, the search needs to check more than one column, people expect it to cope with typos or partial words, or the results need to be ranked by relevance rather than just returned in whatever order the database feels like.

Why a LIKE query breaks down

A LIKE '%term%' search with a leading wildcard can't use a normal database index. The database has to scan every row and check whether the text appears anywhere inside it. On a small table that's instant. On a table with 200,000 invoices, it starts taking a noticeable second or two, and it gets slower every month as the table grows. I've seen this exact pattern show up as "the search is getting slow" tickets, which is really just the database doing a full table scan on every keystroke.

It also doesn't rank anything. If someone searches "Smith plumbing invoice", a LIKE query either finds an exact match for that whole phrase or finds nothing useful at all. It has no concept of "this result matches two of your three words, so show it near the top". Real search needs to treat the query as a set of meaningful words, not one long string.

And it doesn't handle the small human things people expect: searching "Mcdonald" and finding "McDonald's", searching a partial phone number, or searching a misspelled surname and still getting a sensible result. Google trained everyone to expect forgiving search. A basic LIKE clause is the opposite of forgiving.

What proper search actually needs

Both MySQL and Postgres have built-in full-text search features (FULLTEXT indexes in MySQL, tsvector columns in Postgres) that are a big step up from LIKE. They tokenise text into words, build a proper index so lookups stay fast as the table grows, and can rank results by relevance instead of just matching or not matching. For a huge amount of business software, that's genuinely enough.

Getting it right still takes some thought though. You need to decide which fields actually belong in the search: a customer's name and email, probably; their internal notes, maybe not. You need to decide how fields are weighted, so a match on a customer name counts for more than a match buried in an old comment. And if search needs to span more than one table, say customers, orders and invoices all in one search box, that needs a plan for how those results get combined and ranked together, not three separate searches bolted onto one input.

For some businesses, even a well-built database search isn't enough. If you're running an ecommerce catalogue with faceted filtering, or a document library where staff need to search inside PDFs, a dedicated search service like Elasticsearch or Algolia earns its keep. That's more infrastructure to run and pay for, so it's worth checking the built-in database options can't do the job first. Most internal tools and admin systems never actually need it.

Questions worth asking before you commission it

• Which fields should search actually cover, and which shouldn't be searchable at all?
• Does search need to span more than one table or type of record?
• How big will this table realistically be in two years, not on launch day?
• Does it need to tolerate typos and partial matches, or is exact matching acceptable?
• Should results be ranked, or is a simple filtered list good enough?

None of this needs to go in the first draft of a brief. But it's worth flagging "search" as a feature that needs a short conversation rather than a single tick box, the same way I'd flag permissions or reporting. A search box that quietly slows down or returns nothing useful erodes trust in the whole system faster than almost any other feature, because people use it every day and notice immediately when it lets them down.

If you're scoping a system that leans heavily on search, whether that's an internal tool with growing tables or a customer-facing product, it's the kind of detail that changes the build slightly. If you'd like a second opinion on how a search feature should actually work before you commit it to a spec, get in touch and I'll take a look at what you're planning.