When SQL LIKE Stops Working: Choosing Search for a Large Website
Short Answer
SQL LIKE search suits small sites, database full-text adds ranking, and a dedicated search index adds typo tolerance, filters and Arabic normalisation. Switch when search data shows visitors failing.
A content site with a few hundred pages can run search straight from its database without anyone noticing. As the archive grows into thousands of articles, products or listings, the same box can return slow, oddly ordered or empty results. Visitors type a misspelt word, get nothing back and leave. So when does a dedicated search index earn its running cost?
This guide compares three options for UAE business websites: plain SQL LIKE queries, the database's own full-text features and a separate search engine. It explains what each can and cannot do, with references to the MySQL, PostgreSQL and WordPress documentation. It also covers Arabic queries, which bring their own matching problems. You will finish with clear signals for switching.
Why does LIKE search slow down as a site grows?
A LIKE query starting with a wildcard forces the database to check every row's text. The MySQL 8.4 manual states that a B-tree index helps LIKE only when the pattern does not begin with a wildcard. Matching words anywhere in a page needs that leading wildcard.
WordPress core search works this way, wrapping each term in wildcards across post titles, excerpts and content. That suits a small blog, but work grows with each page. LIKE also returns any row containing the letters, so searching art matches part, cart and smart.
Quick Answer
On MySQL, a LIKE search with a leading wildcard cannot use a normal B-tree index, so every search scans the text. WordPress core search uses exactly this pattern by default.
What can database full-text search fix first?
Full-text indexes are the cheapest upgrade, because they run inside the database you already pay for. MySQL FULLTEXT search matches whole words and returns a relevance score, so stronger matches appear first. On PostgreSQL, the pg_trgm extension can even index LIKE patterns that start with wildcards.
Those limits still matter for the visitors using your search box. By default, MySQL InnoDB full-text ignores words shorter than three characters and skips common stopwords. Natural language mode matches words as typed, so someone searching for resturant will not find your restaurant pages.
Quick Answer
Database full-text search adds whole-word matching and relevance ranking without a new service. MySQL still ignores short words and stopwords by default, and it does not correct misspelt search queries.
What does a dedicated search engine add?
A dedicated engine such as Meilisearch or Elasticsearch keeps a separate index built purely for fast search. It adds typo tolerance, synonyms, filters and language-aware matching. Meilisearch accepts one typo by default in query words of five or more characters.
Arabic is where the gap shows on UAE sites. Elasticsearch's Arabic analyzer runs an Arabic normalisation filter, which Lucene documents as mapping hamza forms of alef to bare alef and removing diacritics. Depending on the database collation, plain LIKE matching can treat those variants as different words, splitting the results.
Quick Answer
A dedicated search engine adds typo tolerance, synonyms, filters and language-aware matching. For bilingual UAE websites, its Arabic normalisation matches letter variants that LIKE queries may treat as different words.
How do you know it is time to switch?
Switch when search data shows visitors failing, not when the archive passes a round number. Turn on GA4 enhanced measurement, which can record a viewsearchresults event with the search term. Then review which search terms lead to exits or immediate repeat searches.
Log zero-result queries on the server as well, so you see exactly which words returned nothing. Check the database slow query log for search requests during busy periods. Misspellings, Arabic variants and filter requests point to a dedicated engine, while slow queries alone may only need full-text indexing.
Quick Answer
Switch to a dedicated search index when search data shows failure, such as zero-result queries, misspellings or Arabic mismatches. Slow queries on their own may only need database full-text indexing.
What does running a separate search index involve?
A separate index is a second system to host, secure and keep in sync with your content. Every new, edited or deleted page must reach the index, through a webhook or a scheduled job. Plan a full reindex for whenever index settings change.
Keep the keys separate too. Meilisearch's security guide says the default search key is meant for front-end use, while the admin key must never appear on a public front end. Decide what visitors see if the search service goes down, such as a fallback to database search.
Quick Answer
Running a dedicated search index means hosting it, syncing every content change and planning reindexes. Use a search-only key in browser code, and plan a fallback if search ever fails.
Main Takeaways
A SQL LIKE query with a leading wildcard scans every row and cannot rank results, which suits small sites only. Database full-text search adds whole-word relevance ranking without any new infrastructure. A dedicated index adds typo tolerance, filters and Arabic normalisation, but needs syncing, secure keys and a fallback. Let GA4 search terms and zero-result logs decide when to switch.
Picking search that fits your content
Site search problems can stay hidden, because visitors who find nothing simply leave without complaining. Start by measuring what people type and which searches return empty. Then fix the cheapest layer that solves the failure, moving from LIKE to full-text before adding new infrastructure. A dedicated index is worth its upkeep once misspellings, Arabic variants or filters start costing enquiries.
Auronix Web Studio can review your current search setup, read your search data and recommend the lightest option that works. Search architecture is planned as part of our business website development service, including Arabic and English content. Share your site address and a few searches that currently fail for visitors. We will tell you whether full-text indexing is enough first.
Sources & References
FAQs
Questions readers usually ask next
These FAQs are written to match the topic of this post and to help readers move from understanding to action.
Is WordPress built-in search good enough?
For a small site it can be, because LIKE queries over a modest table still return quickly. As content grows, it struggles more with relevance, misspellings and Arabic spelling variants.
What is the difference between LIKE and full-text search?
LIKE checks whether a string of letters appears in the text, with no ranking. Full-text search indexes whole words and scores each result, so stronger matches can be listed first.
Can MySQL full-text search handle typos?
Not on its own, because natural language mode matches words as typed. PostgreSQL's pgtrgm similarity functions or a dedicated engine with typo tolerance are the usual ways to add it.
Can one search box handle Arabic and English together?
Yes, if each language is analysed properly, for instance through separate fields or analyzers. Test common Arabic spellings, transliterated English terms and mixed queries before launch, not after complaints arrive.
How do I find out what visitors search for?
GA4 enhanced measurement can capture site searches from URL query parameters such as q, s or search. Our GA4 tracking guide for UAE websites explains the event setup and reporting.
How much does a dedicated search engine cost to run?
Self-hosting an open-source engine costs server resources plus your ongoing maintenance and update time. Hosted providers charge on their own published measures, such as records stored or monthly search requests.
Will a search engine slow down my website?
It can reduce database load, because search requests stop competing with page queries. Latency to the search host still matters, so choose a region close to your visitors where possible.
What happens to search results when content changes?
The index only knows what it has been sent, so edits must sync automatically. Without that, deleted pages keep appearing in results and new pages stay hidden until a reindex.
Can site search show filters such as price or location?
Yes, dedicated engines support filters and facet counts built from fields in each record. Plan those fields during the build, because adding filters later can mean restructuring and reindexing everything.
Should search be tested under load before a campaign?
Yes, because one campaign can push many visitors through search and filters together. Include search journeys when load testing before a launch spike and watch database and search response times.
Related Resources
Need help choosing the right stack?
We help UAE businesses keep public pages fast, indexable, and conversion-friendly, then reserve heavier frameworks for the parts that actually need them.
Built for search-ready UAE websites that need clarity, speed, and trust.
