Post reply

Making a better website search

  • December 19, 2014 at 10:33 am

    #299583

    Hi, I wanted some general advice regarding making a better search facility on a website.

    At the moment my search looks something like this:

    CREATE PROCEDURE dbo.Product_Search

    -- Add the parameters for the procedure here

    @String1 VARCHAR(50)

    AS

    BEGIN

    SET NOCOUNT ON;

    SELECT ProductID,ProductName,ProductDescription,Price,ProductURL,ProductImage,ProductCategory

    FROM MyTable WHERE ProductName LIKE '%' + @String1 + '%' OR

    ProductKeywords LIKE '%' + @String1 + '%' OR

    Productdescription LIKE '%' + @String1 + '%';

    END

    So I am basically just finding exact matches for the string being searched for.

    I was considering trying to chop the search string up by looking through the string for spaces and then looking first for an exact match as above, then looking for matches where all 2 or 3 (or more) shorter strings are there, then looking for when only 1 or more of the shorter strings are there.

    This will give much better results and obviously I am not trying to build another google but is there an even better way to get better results?

    I record all searches and have started replacing wrong spellings using ASP REPLACE before submitting to SQL but I think I would be better creating a table of misspellings with the correct spellings and doing it in SQL itself somehow?

    Any thoughts welcome, I am sure plenty of people have done this sort of thing before.

  • December 19, 2014 at 12:39 pm

    #1765787

    i think this is where you need to look into using a full text indexing. that's the way to really handle these kinds of searches.

    doing it via LIKE statements requries a massive table scan, every single time, because no index can be used to limit the results.

    take a look at this article from four years ago(already?)

    that offers some guidance:

    A Google-like Full Text Search

    By Michael Coles, 2010/05/31 (first published: 2008/10/07)[/url]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply