|
|
Posted by Bryan on 10/05/15 11:28
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
Navigation:
[Reply to this message]
|