Link to home
Start Free TrialLog in
Avatar of ZuZuPetals
ZuZuPetalsFlag for United States of America

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(BookTitleTable, *, 'Bible')
            union
            SELECT [key],25 as Points
            FROM CONTAINSTABLE(BookDescriptionTable, *, 'Bible');

      select top 10 BookID,sum(Points) as SumPoints
            from #SearchResults
            group by BookID
            order by SumPoints desc;

Thanks in advance,
Pete

ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ZuZuPetals

ASKER

I haven't looked at the actual execution plan yet; I'll do that.