Search Advice

I'm looking for some advice on the best way to execute a complicated
search on my eCommerce web site. My example here is much simpler than
the real thing, but it should be enough to get the point across.

Imagine that I have two tables:

Products Table
id as Integer
code as VarChar(10)
name as VarChar(50)
mfr as Integer
description as Text

Manufacturers Table
id as Integer
name as VarChar(25)

I need to write a keyword search that will return matches on the
product code, product name, product description or manufacturer name. I
could do a simple join and use a lot of LIKE clauses, but it's not very
efficient.

SELECT p.id
FROM Products p INNER JOIN Manufacturers m
ON p.mfr = m.id
WHERE p.code LIKE '%Keyword%'
OR p.name LIKE '%Keyword%'
OR p.description LIKE '%Keyword%'
OR m.name LIKE '%Keyword%'

Having those wildcards at the start of the like kill performance.

So next I thought, maybe I join all of the fields together, then do the LIKE. For example:

SELECT p.id
FROM Products p INNER JOIN Manufacturers m
ON p.mfr = m.id
WHERE p.code + p.name + p.description + m.name LIKE '%Keyword%'

This is certainly better than above, but it still doesn't seem ideal.

Can anyone suggest something I might not be thinking of? The product
codes tend to have dashes, periods and numbers that preclude the
Full-Text search features of SQL Server, so I'm grasping at straws for
a better approach...


Thanks in advance....


- Bryan