ZuZuPetals
asked on
MS Search Service: Supports weighted search on WHERE the data matched? (not search terms)
Does the Search Service support weighted search on WHERE the data matched, rather than weights on the search terms themselves?
I need a solution that returns weighted search results for users trying to find books on a database-driven website. For terms typed by the user, if the term matches the book's title then 100 points are contributed to the relative ranking figure. On the other hand, if it matches the book's description, I only want, say 25 points to contributed to the ranking:
"Book" Table:
BookID, PK int
BookTitle, varchar(20)
BookDescription, varchar(8000)
Example search: The Bible
I want book titles containing "the" and "Bible" to match first, followed by books containing "the" and "Bible" in the description.
Even more importantly, it would be nice to mathematically sum the matching points so that if a book description had "the" and "Bible" many times in the description those results would even appear higher in the search results than "the" and "Bible" in the titles.
We're eager for a solution for this that is "Google" fast!
--> this seems like a fairly common thing to do on e-commerce sites so I'm wondering if I'm missing something.... Does the Search Service have this type of search built-in?
I've researched and played with the WEIGHT keyword but I can't figure out how to pull off this kind of summed search result using WEIGHT.
Loosely, following is the best I could come up with on my own but it's slow... certainly not "Google" that customers expect these days! We only have a million books in our database but the stored proc takes seconds not milliseconds to run:
ALTER PROCEDURE [io].[BookSearch]
as
/* SET NOCOUNT ON */
CREATE TABLE #SearchResults (
BookID int,
Points int );
insert into #SearchResults (BookID,Points)
SELECT [key],100 as Points
FROM CONTAINSTABLE(BookTitleTab le, *, 'Bible')
union
SELECT [key],25 as Points
FROM CONTAINSTABLE(BookDescript ionTable, *, 'Bible');
select top 10 BookID,sum(Points) as SumPoints
from #SearchResults
group by BookID
order by SumPoints desc;
Thanks in advance,
Pete
I need a solution that returns weighted search results for users trying to find books on a database-driven website. For terms typed by the user, if the term matches the book's title then 100 points are contributed to the relative ranking figure. On the other hand, if it matches the book's description, I only want, say 25 points to contributed to the ranking:
"Book" Table:
BookID, PK int
BookTitle, varchar(20)
BookDescription, varchar(8000)
Example search: The Bible
I want book titles containing "the" and "Bible" to match first, followed by books containing "the" and "Bible" in the description.
Even more importantly, it would be nice to mathematically sum the matching points so that if a book description had "the" and "Bible" many times in the description those results would even appear higher in the search results than "the" and "Bible" in the titles.
We're eager for a solution for this that is "Google" fast!
--> this seems like a fairly common thing to do on e-commerce sites so I'm wondering if I'm missing something.... Does the Search Service have this type of search built-in?
I've researched and played with the WEIGHT keyword but I can't figure out how to pull off this kind of summed search result using WEIGHT.
Loosely, following is the best I could come up with on my own but it's slow... certainly not "Google" that customers expect these days! We only have a million books in our database but the stored proc takes seconds not milliseconds to run:
ALTER PROCEDURE [io].[BookSearch]
as
/* SET NOCOUNT ON */
CREATE TABLE #SearchResults (
BookID int,
Points int );
insert into #SearchResults (BookID,Points)
SELECT [key],100 as Points
FROM CONTAINSTABLE(BookTitleTab
union
SELECT [key],25 as Points
FROM CONTAINSTABLE(BookDescript
select top 10 BookID,sum(Points) as SumPoints
from #SearchResults
group by BookID
order by SumPoints desc;
Thanks in advance,
Pete
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER