Contact Us menu-bars menu-close

Full Text Search


Ashish Jain

June 02, 2011

Performance is a major concern in every application. Here, I am describing a crucial feature of SQL by which we can improve the performance of our applications that offer search functionalities.

I got a chance to get familiar with “Full Text Search” due to a requirement in my last project. There was a need in my project where I had to maintain millions and millions of records in the database.

The main challenge that I faced was to provide a search page where the end user will enter a word and I had to pull all those database records which have any data matching with the word entered by the user. To achieve this, I was previously using the “LIKE” operator to look for matching rows against millions of records. But every time I would search any record, the performance of my application would go down and the fetching process was taking almost a minute to return the results.

After talking to some database gurus and reading several articles on the internet, I realized that SQL has a great feature called “Full Text Search”. What I had to do was, replaced the “LIKE” operator by “Full Text Search” and voila! I was able to improve the performance of my application by a huge margin! Now the search functionality was able to pull and display results in 5 to 7 seconds instead of 60.

Now to clarify the technical difference: The “LIKE” operator uses a character pattern search whereas “Full Text” is used for word searches.

The lesson is, “LIKE” should be only be used when we have a requirement to get results by character patterns and I would personally suggest the use of “Full Text Search” every time when we have to deal with large amount of data.

Get updates. Sign up for our newsletter.


Let's explore how we can create WOW for you!