SQL Server Quick Search for 40M Text Entries

Question

I have a SQL Server 2005 database with a table containing 40 million records. Each entry contains a column that stores a list of keywords separated by commas. Each keyword is a combination of letters and numbers. Keywords are up to 7 characters long, and on average one entry per 15 keywords. Keywords are not unique across the lines.

I want to search for the full or part of a keyword.

I created a full text index that shows 328,245,708 unique key counters. Search efficiency is suitable for queries with 4 or more characters (about 100 ms on a test computer), but too slow for queries with 3 or less characters (up to 3s on a test machine).

I tried to execute the CONTAINSTABLE and CONTAINS queries sorting '[query]*' with a similar result.

I believe that executing short queries is slower because short words are repeated more often in different entries.

Sorting the results is not critical, and I'm trying to get TOP X results sorted by Rank from CONTAINSTABLE . This does not provide the desired performance.

How to speed up this search for short queries?

Answer 1

You might want to install it in something like SOLR or Sphinx and let the specialized text search engine handle the search functions.

Answer 2

Another option is to consider normalizing the structure of the table so that you have data records in one table, tags in another table, and an associative table to bind tags to data records. This will allow you to perform a text search on tags and simply join the associative table to get the relevant records.