Sql-server – Using LIKE or Full-text when searching database

sql serversql-server-2012

I am fairly new to SQL and I am currently trying to create a search function for my website, much like the one on imdb.com. I have 3 tables and I need to search two rows (nvarchar) in each table when a user makes a search. The data to be searched is maximum 20 letters long but there are a lot of rows, and hopefully many more will be added.

I want to "future-proof" the website and I was looking up Full-text and FREETEXT searches. I have been using LIKE before but it says this is not very optimal for performance when the search has to look through a lot of data.

So basically before I start working on this very important part of my website I want to know what experts like you think. Is it acceptable to use LIKE when making a big search or should I learn how to use Full-text indexes and FREETEXT searches?

My current way of searching:

SELECT table1.col1, table1.col2
FROM table.1
WHERE table1.col1 LIKE '%@search%'
UNION ALL
SELECT table.2.col1, table2.col2
...

Database version:
Microsoft SQL Server 2012 – 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 (Build 9600: )

EDIT: The data are always just 1 word, so there will be no use for stopwords that are provided by full-text.

Best Answer

Having a wildcard in front of your search term will prevent SQL Server from being able to use an index and thus become painful very fast. 2 things you could do, besides going for full text search:

  1. The obvisous one, do not put a wildcard at the start of the keyword requiring the user to enter a search query matching the start of col1.
  2. Splitting col1 from both tables into keywords and put the distinct keywords in a sperate table and add two junction tables joining table1 and table2 to the keyword table.
Related Question