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%'
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.

  • As I said in the question there is not much at the moment. But the website allows users to add data to one of the tables. Actually it's the main function of the website. So there might be a lot eventually – Green_qaue Feb 22 '15 at 19:28
  • NB You'll have to install Express with Advanced Services for Full-text to work. – wBob Feb 23 '15 at 10:57
  • @wBob a bit unrelated but if I search the "name" column in a table. Is it okey to create a clustered index for just "name" then? and leave out the primary key? I insert things in this table aswell but the main function is to search the "name" column. – Green_qaue Feb 24 '15 at 16:26

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.
  • Thank you for taking the time to answer. Had no idea about the wildcard effect. So basically it's better for performance to use '@search%' than '%@search%'? – Green_qaue Feb 22 '15 at 19:24
  • Absolutely, for the former and index may be used. PS: you need to concat % to your variable, like so: @search + '%' – MWillemse Feb 22 '15 at 19:32
  • I know about the concat but thanks :) Just to make things super clear, SQL server handles the indexing in this situation automatically correct? – Green_qaue Feb 22 '15 at 19:35
  • SQL Server will use the index automatically if you've created one. It will not create an index for you. – MWillemse Feb 22 '15 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.