Use of "Like" in the where clause slow down my query

Question

For our website we have given our customer to search data,result,story etc where in we are using like in our query where search text is search on our table and this is causing performance issue and high cpu utilization.So what is the best way to search users query into our sql table other than using like and which does not penalized our query performance and server.Pls suggest.e.g some thing like select * from article where sStory like '%search string from user%'

Answer 1

It's hard to give advice without knowing the use case and the requirements. However, I wrote a tip about LIKE for the SQLServerGeeks Magazine, and it is now also avilable as a blog post:
https://sqlservergeeks.com/a-tip-to-optimise-like-searches-by-erland-sommarskog/.

The other alternative is to investigate full-text, which I briefly discuss in the blog post. I also briefly touch at a third alternative - and not that it is heavy artillery.

Answer 2

Hi @RajeshK ,

Welcome to Microsoft Q&A!

Like '%ABC%' will always perform a full table scan.

You could potentially see performance improvements by adding index(es), it depends a lot on the specifics. For example:

create index myindex on article(sStory)  

In addition, you could also try with Full Text Searching (using the CONTAINS) which will be faster/more efficient than using LIKE with wildcarding. Full Text Searching (FTS) includes the ability to define Full Text Indexes, which FTS can use.

You could start with FTS in this article.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.