tags:

views:

40

answers:

1

I am making a synonyms list that I will store it in database and retrieve it before doing full text search.

When users enters like: word1

I need to lookup for this word in my synonyms table. So if the word is found, I would SELECT all the synonyms of this word and use it in the fulltext search on the next query where I contruct the query like

MATCH (columnname) AGAINST ((word1a word1b word1c) IN BOOLEAN MODE)

So how do I store the synonyms in a table? I found 2 choices:

  1. using key and word columns like

    val  keyword
    -------------
    1    word1a
    1    word1b
    1    word1c
    2    word2a
    2    word2b
    3    word3a
    etc.
    

So then I can find exact match of the entered word in one query and find it's ID. In the next select I get all the words with that ID and somehow concate them using a recordset loop in server side langauge. I can then construct the real search on the main table that I need to look for the words.

  1. using only word columns like

    word1a|word1b|word1c
    word2a|word2b|word2c
    word3a
    

Now I so the SELECT for my word if it is inside any record, if it is, extract all the record and explode it at | and I have my words again that I can use.

This second approach lookes easier to maintain for the one who would make this database of synonyms, but I see 2 problems:

a) How do I find in mysql if a word is inside the string? I can not LIKE 'word1a' it because synonims can be very alike in a way word1a could be strowberry and strowberries could be birds and word 2a could be berry. Obviously I need exact match, so how could a LIKE statement exact match inside a string?

b) I see a speed problem, using LIKE would I guess take more mysql take than "=" using the first approach where I exact match a word. On the other hand in the first option I need 2 statements, one to get the ID of the word and second to get all the words with this ID.

How would you solve this problem, more of a dilemma which approach to take? Is there a third way I don't see that is easy for admin to add/edit synonyms and in the same time fast and optimal? Ok I know there is no best way usually ;-)

UPDATE: The solution to use two tables one for master word and second for the synonym words will not work in my case. Because I don't have a MASTER word that user types in search field. He can type any of the synonyms in the field, so I am still wondering how to set this tables as I don't have master words that I would have ID's in one table and synonims with ID of the master in second table. There is no master word.

+5  A: 
lexu
You mean 2 tables: word table with WordID and word columns and a synonims table with WordID and synonim column? And I can search a word table and join it with synonyms table to get the results in a recordset that I loop then and put them together somehow?
Jerry2
You are correct ... I was trying to add that to my answer while you commented.
lexu
SELECT synonim FROM wordtable w LEFT JOIN synonymstable sy ON sy.WordID=w.WordId where word = 'mysearchword'? Is there a way to get results back in ONE row not number of synonims rows? To concat the rows somehow in the same SELECT?
Jerry2
I think you made a very good point yes about the tables. I can then select only once. If there would only be a way to somehow CONCAT the rows into one row to get the result as word1a word1b word1c somehow so it is easier to enter into a main search query (no RS loop would be needed then)
Jerry2
I think I found answer to my own question: It may be group_concat is what I am looking for ;-)
Jerry2
Seems my problem is still not solved, please take a look at my update post.
Jerry2
@Jerry2: please see my explanation/example how the two table approach was intended
lexu
Thanx, this works well if you have 2 synonims, as you need 2 entries in synonims table. But if you have like 10 synonims you would have to store... all the combinations in the second table? That would make hundreds... or I don't understand properly. Can you please make example with 3 synonims of a word?
Jerry2
@Jerry2: I've added a 3rd synonym... and yes, there would/could be hundreds of entries, but since they actually reflect the relationships between the words, and you want to be able to search for each word, this can't be helped. "Space saving" approaches, like the '2nd approach' I mentined, aren't any better (space, performance, complexity), if you look at them closely.
lexu
It's only hard to manage this database if I add or remove any synonyms, but I guess this can not be helped.
Jerry2