Posted by James A on 03/28/07 12:02

Hi,



I want to allow full text search of a row in a table (MySQL v3.23). I
understand that the WITH QUERY EXPANSION clause adds ability for results to
be returned even where the match is not exact, misspellings, and some
equivalences.



However, I would like to specify somehow sets of equivalent words, so for
example a search for "cat" would return a row containing the word "cat",
"kitten" or "animal". The database is not large (300 rows with average of
100 words per row) and it is specialized so this list would not be huge.



Is there a way to customize the MySQL query expansion to add these
alternative words? I do not have access to the source, so I don't expect so.



Otherwise, could someone please suggest a way it could be performed? My
thoughts are to construct a table of equivalent words, and then use code to
expand the search string before performing the search, so that e.g.



SELECT * FROM table WHERE MATCH (row) AGAINST ('cat')



would become



SELECT * FROM table WHERE MATCH (row) AGAINST ('cat kitten animal')



However, I want to lsit the results in terms of relevance and I think that
would return badly weighted results - rows with more than one word in the
search phrase would be classed as more relevant than those with just one,
whereas I am trying to make the words equivalent to each other. (I also want
to do the same with phrase matching which could complicate it further
maybe.)



Many thanks in advance to anyone who has any ideas or has done such a thing
already.



--

James

[Back to original message]