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.