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

if you are 100% sure you cannot sue full text search,
do it yourself:
for instanse, for row
ID Make Model
1 Toyota Camry

add these row to an additional table (ID, TOKEN)

1 TOYOTA
1 OYOTA
1 YOTA
1 OTA
1 TA
1 A
1 CAMRY
1 AMRY
1 MRY
1 RY
1 Y

etc.

that done, instead of MODEL LIKE '%OYO%' or MAKE LIKE '%OYO%' you can
use

TOKEN LIKE 'OYO%' which is sargeable

Maintaining tokens is another story, long and boring

Huh.

Well, I hadn't considered that approach before. I guess it would work,
but it would make for a HUGE database. The web site I need this for
has 41,000 products and 1,100 manufacturers. Just using some basic
math, that means:

41,000 codes +
41,000 product names * 5 words per name (average) +
41,000 product descriptions * 50 words per description (average) +
1,100 manufacturers
--------------------------------------
2,297,100 words

Then you have to take each word, and break it down into parts. That
could mean BILLIONS of entries in the database.

I'm sure this approach would work, but it's not really practical for my
situation. Thanks for the idea though.

Anyone else have any suggestions or ideas?

>codes tend to have dashes, periods and numbers that preclude the
>Full-Text search features of SQL Server

what if you replace dashes, periods, and numbers with some unicode
characters, and then try to use the ready made text search?
Or just replace 5.4-1 whith fivedotfourdashone?