1

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.

3
  • 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
    Commented Feb 22, 2015 at 19:28
  • NB You'll have to install Express with Advanced Services for Full-text to work.
    – wBob
    Commented Feb 23, 2015 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
    Commented Feb 24, 2015 at 16:26

1 Answer 1

3

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.
4
  • 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
    Commented Feb 22, 2015 at 19:24
  • 1
    Absolutely, for the former and index may be used. PS: you need to concat % to your variable, like so: @search + '%'
    – MWillemse
    Commented Feb 22, 2015 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
    Commented Feb 22, 2015 at 19:35
  • SQL Server will use the index automatically if you've created one. It will not create an index for you.
    – MWillemse
    Commented Feb 22, 2015 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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