Full Text Search

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.