I read and used an excellent tip on: http://www.sql-server-performance.com/tb_search_optimization.asp.
The basic problem revolved around full text searches using WHERE predicates.For example:
FROM ads p
INNER JOIN containstable(properties, description, 'car') t
ON p.PropertyID = t.[key]
WHERE p.type = 'red'
This returns, 1,200,000 records back to the optimizer, which is then filtered down to a further 150,000 'cars' that are 'red'.
The solution proposed in the article, uses embedded text in the text column, and
then some substring manipulation to take out the embedded text.Inelegant, but very effective
Republished from http://www.sqlserver-dba.com.