{"id":5764,"date":"2018-09-04T10:25:40","date_gmt":"2018-09-04T17:25:40","guid":{"rendered":"http:\/\/www.couchbase.com\/blog\/?p=5764"},"modified":"2025-06-13T20:27:48","modified_gmt":"2025-06-14T03:27:48","slug":"why-you-should-avoid-like-deep-dive-on-fts-part-1","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/why-you-should-avoid-like-deep-dive-on-fts-part-1\/","title":{"rendered":"Why you should avoid LIKE % | Deep Dive on FTS – Part 1"},"content":{"rendered":"

Search was probably one the most overlooked features in past applications. However, in recent years, it has really gotten all the attention it deserves as we have finally realized how it can increase sales or shape user\u2019s behavior and engagement.<\/p>\n

In this new blog series, I would like to go from zero to expert. As such, at the end of it, you will be able to implement searches \u201clike a pro<\/em>\u201d.<\/p>\n

 <\/p>\n

Why \u201cLike %\u201d isn\u2019t good enough?<\/strong><\/h2>\n

A well-implemented search should essentially be fast and relevant; “Like %<\/strong>” is neither of them. Let\u2019s see why:<\/p>\n

 <\/p>\n

The problem with Speed<\/strong><\/h3>\n

Let\u2019s say we would like to implement a search for a movie according to its title. A naive SQL query looking for all \u201cStar Trek\u201d movies would look like the following:<\/p>\n

Select * from Movies where title like \u201cStar Trek%\u201d<\/pre>\n
By default, it will execute a full table scan trying to match this term to all rows. But it can be optimized with an index, which will create most likely a B-Tree structure. Therefore, even searches like the following would still partially leverage the index:<\/p>\n
Select * from Movies where title like \u201cStar%Trek\u201d\r\nSelect * from Movies where title like \u201cSt%Trek\u201d<\/pre>\n
But what if the user is a big fan of Batman and decides to search for the term \u201cDark Knight\u201d? According to the previous example, our query will look like the following:<\/p>\n
Select * from Movies where title like \u201cDark Knight%\u201d<\/pre>\n
The search above has the won\u2019t bring any results as the real name of the movie is \u201cThe<\/strong> Dark Knight Rises\u201d.\u00a0 To fix this problem, let\u2019s add a wildcard at the beginning of our search term:<\/p>\n
Select * from Movies where title like \u201c%Dark Knight%\u201d<\/pre>\n
Fixed! Right!?\u00a0 Unfortunately, not really. The query above will fina a result like \u201cThe<\/strong> Dark Knight Rises\u201d, but it won\u2019t leverage the index anymore and definitely won\u2019t perform well at scale. Like %<\/strong> does not \u201cunderstand\u201d the content of your field. Rather, it treats a text as a single thing, which makes it not the best choice to deal with anything that has some sort of underlying structure.<\/p>\n
A possible solution is to use MATCH, but it has also been reported to be slow<\/a> in some databases.<\/p>\n
 <\/p>\n
The problem with Relevancy<\/strong><\/h3>\n
What if we\u2019d like to also search for the movie\u2019s overview? A naive solution would be to just add a new field to the query:<\/p>\n
Select * from Movies where title like \u201c%Dark Knight%\u201d or overview like \u201c%Dark Knight%\u201d<\/pre>\n
This approach introduces a new problem: any movie with mentions “Dark Knight” in the overview has the same relevance as the ones with it in the title, and the order of the results are totally uncertain.<\/p>\n
A common mistake is to think that UNION DISTINCT could solve this problem. However, most of the query planners nowadays execute each block of the union in parallel, which will again mess up with the order\/relevancy.<\/p>\n
If you really want to implement some sort of relevancy using plain SQL, a simple solution is to execute two separate queries and append the result of one to the other. Of course, this strategy is far from optimal and you\u2019d still need to handle duplication manually.<\/p>\n
The examples above show one of the unspoken truths about search: The real challenge in search is not finding a match but how to sort them<\/strong><\/em>. After all, matching a text is a very straightforward task, but giving it the right score is something that should be carefully crafted.<\/p>\n
 <\/p>\n
Full-Text<\/strong> search comes to the rescue!<\/strong><\/h2>\n
There are many different ways to search for a term, each one focusing on a specific strategy. We will cover the majority of them in the upcoming articles, but for now, let\u2019s focus on why FTS is so much better than \u201cLike %<\/strong>\u201d:<\/p>\n
 <\/p>\n
The solution for Speed<\/strong><\/h3>\n
In order to be able to query a database using FTS, you must create an inverted index first. This index, roughly speaking, is a map of words and their occurrences:<\/p>\n
\"inverted_index\"<\/p>\n
With the index above in place, searching for a word becomes a simple task, as you can easily get only the documents which contain the target term.<\/p>\n
Notice that during the creation of the inverted index, we have converted phrases to an array of words, so wildcards aren\u2019t necessary, as we can directly match the target terms against our inverted index. Nonetheless, even the use of wildcards here (Ex: *star*) will still run faster at scale, as you will only need to iterate through your index to find all matches instead of scanning all documents in your database.<\/p>\n
I haven\u2019t mentioned anything about dictionaries, stemming, analyzers, tokenizers and synonymous yet. I will get there in part 2 of this series.<\/p>\n
 <\/p>\n
The solution for Relevance<\/strong><\/h3>\n
The strategy to implement a relevant search result might vary according to the domain you are working on. In general, the relevance can be manipulated in different multiple ways, the simplest one is called boosting. Roughly speaking, boosting is just a simple way to assign a weight for your field matches:<\/p>\n
Query:<\/strong><\/p>\n
Title:”<\/strong>Star Trek”^2 | Overview:”<\/strong>Star Trek”<\/p>\n
In the example above, the score of a match for \u201cStar Trek\u201d in the title<\/strong> will be worth twice as much as a match in the overview<\/strong> one. This is basically how you can suggest how your search results should be sorted.<\/p>\n
 <\/p>\n
Why isn\u2019t everybody using Full-Text Search then?<\/strong><\/h2>\n
FTS usually demands you to set up a whole new infrastructure, add new dependencies, create multiple indexes, and then push all your document changes to an external system like Elastic Search<\/a> or Solr, even if you just want to implement a very simple search like our previous example. Thus, developers tend to avoid this huge amount of work until it becomes strictly necessary.<\/p>\n
That is one of the reasons why we have decided to incorporate an FTS Engine called Bleve<\/a> into Couchbase, then all you need to do is to create a new index in the Web Console:<\/p>\n
\"movie_index_creation\"<\/p>\n
And you will automatically be able to make full-text searches using the default Couchbase SDK:<\/p>\n
String indexName = \"movies_index\";\r\nPhraseQuery query = SearchQuery.phrase(\"Star Trek\");\r\nSearchQueryResult result = movieRepository.getCouchbaseOperations().getCouchbaseBucket().query(\r\n    new SearchQuery(indexName, query).highlight().limit(20));<\/pre>\n
 <\/p>\n
Most of the relational databases already have support for full-text search, why not just use them?<\/strong><\/h3>\n
\u00a0<\/strong>Actually, it could be a nice choice for small use cases, and I have heard a few positive feedbacks from developers doing so. My personal recommendation is that you should consider a proper tool, such as Bleve, whenever you have a considerable volume of data, or whenever you will need to scale massively.<\/p>\n
There is another big plus of using a proper FTS engine: the query language. The majority of the relational databases try to reuse SQL for that, and it can get really messy if you try to write advanced searches. After all, SQL wasn\u2019t designed to deal with conjunction\/disjunction queries, facets, complex ranking factors, etc.<\/p>\n
 <\/p>\n
Full-Text Search Series<\/strong><\/h4>\n