Professional Web Applications Themes

Fulltext searches, synonyms, and alternate spellings... - MySQL

I was wondering if there was any way to somehow modify a fulltext search to be able to match on full synonyms and (perhaps more importantly) alternative spellings. For instance, INSERT INTO some_table (words_text) VALUES ('He parked the automobile with its tyres against the kerb.') SELECT * FROM some_table WHERE MATCH (words_text) AGAINST ('car tires curb') ....won't match at all, when any English-speaking human can see it should. Hoping for any recommendations on how to deal with this, -- Dodger...

  1. #1

    Default Fulltext searches, synonyms, and alternate spellings...

    I was wondering if there was any way to somehow modify a fulltext
    search to be able to match on full synonyms and (perhaps more
    importantly) alternative spellings.

    For instance,

    INSERT
    INTO some_table (words_text)
    VALUES ('He parked the automobile with its tyres against the kerb.')

    SELECT *
    FROM some_table
    WHERE MATCH (words_text)
    AGAINST ('car tires curb')

    ....won't match at all, when any English-speaking human can see it
    should.

    Hoping for any recommendations on how to deal with this,

    --
    Dodger

    Dodger Guest

  2. #2

    Default Re: Fulltext searches, synonyms, and alternate spellings...

    On 12 Nov 2006 15:47:46 -0800, Dodger wrote: 

    Wave a magic wand? I speak English (.us subvarint) natively and would
    never expect a search engine to match "tyre" to "tire". As for "kerb" to
    "curb", and "car" to "automobile", well, they're different words with
    different meaning. For example, "curb" is both a noun and a verb. "Kerb"
    is not. "Car" can apply to a railroad wagon, a conveyance hung from
    wires, or part of an elevator, above and beyond an automobile. It's not
    the job of a search function to account for muddling thinking.

    --
    59. I will never build a sentient computer smarter than I am.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  3. #3

    Default Re: Fulltext searches, synonyms, and alternate spellings...


    Peter H. Coffin wrote:
     

    Hi, Peter.

    Thanks for replying. 'Kerb' is the British spelling for 'curb' (as in,
    edge of sidewalk). 'Tyre' is the British spelling for 'tire' (as in the
    rubber part of a car's wheel). Both 'curb' and 'tire' have other
    meanings as well, but they are less likely in some cirstances -- for
    instance, a catalog of toys. 'Car' might have multiple meanings (and
    because of model railroads, have high likelihood even in toys catalogs)
    but, for instance, 'motorcar' doesn't and would be an exact synonym for
    'automobile', and even with 'car' a search for 'train car' would bring
    up train cars at a higher relevance than motorcars, as it should be
    (and allow for '-motorcar' in a boolean search).

    Thing is, I wouldn't *expect* a search engine to match on them either,
    but I *want* one to somehow.

    Actually, I did come up with a sort-of solution since I posted that
    will work in a preprocessing sense. All I have to do is make a table of
    synonyms like so:

    [synonym]
    word varchar(32)
    synonym varchar(32)

    ....then I can add a column 'alternate_word' and add it to the fulltext
    index. Then for each word in synonym, I can update the synonym column
    to include that synonym where the other columns match the word but
    don't match the synonym already.

    That way when someone searches a costume store for 'Armor' they get
    things made by Brits, Aussies and Canadians, too.

    --
    Dodger

    Dodger Guest

  4. #4

    Default Re: Fulltext searches, synonyms, and alternate spellings...

    Dodger wrote: 

    This is a tough one. As you said, you could build a dictionary of
    synonyms and use that to expand your query, but you will have a hard
    time getting good coverage.

    What is most effective in natural language is blind relevance feedback,
    i.e. automatic query expansion using terms from the doents that use
    your initial terms. You will thus find doents that share common
    words with doents talking about cars, tires, and curbs, which will
    hopefully include doents talking about automobiles, tyres, and
    kerbs. However, this is obviously far from perfect and only works when
    you have a large doent collection, including doents that match
    your initial query.

    You can use this in MySQL by using the "WITH QUERY EXPANSION" option.
    You will find more doentation by googling for this option.

    HTH, HAND,
    Jens

    Jens Guest

Similar Threads

  1. Fulltext Index and ft_min_word_len
    By PKalos@gmail.com in forum MySQL
    Replies: 0
    Last Post: September 30th, 12:27 PM
  2. MySql, PHP FULLTEXT problem
    By ....hooligan in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: June 21st, 07:13 AM
  3. Synonyms to an SE database
    By Andy in forum Informix
    Replies: 11
    Last Post: December 3rd, 05:12 PM
  4. I need to be able to check words and spellings
    By migz in forum Macromedia Director Lingo
    Replies: 0
    Last Post: July 9th, 11:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139