Fast performance when searching for strings in a 100 million record database

Question

Is it possible to get a result within a few seconds? I am using SQL server and have a large table contains millions of records. I wish to search on field called keyword, but performing a FREETEXT or even CONTAINS search when using full text is very slow (10s of seconds). Is there any fast approach to being able to rapidly find words using substrings (i.e. type 'bolt' and 'thunderbolt' be returned) in a quick and efficient manner on this scale?

Thanks Thomas

Answer 1

Create a non-clustered index for the column keyword on which you perform like search.

CREATE NONCLUSTERED INDEX IX_keyword
    ON table (keyword); 
GO

More