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:
BookID, PK int
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]
/* SET NOCOUNT ON */
CREATE TABLE #SearchResults (
Points int );
insert into #SearchResults (BookID,Points)
SELECT [key],100 as Points
FROM CONTAINSTABLE(BookTitleTable, *, 'Bible')
SELECT [key],25 as Points
FROM CONTAINSTABLE(BookDescriptionTable, *, 'Bible');
select top 10 BookID,sum(Points) as SumPoints
group by BookID
order by SumPoints desc;
Thanks in advance,