MySQL Fulltext Search

I’m currently implementing a small fulltext-search feature into my app. I use this boolean-search pattern which works well:


SELECT `title`,

   MATCH (`title`) AGAINST ('example search string') as score FROM `entry`

   WHERE MATCH (`title`) AGAINST ('+example +search +string' IN BOOLEAN MODE)

   HAVING score > 0.2 ORDER BY score DESC

Now I’m wondering how to implement a “related words feature”. What I want is a list of words that are also in the title of the already found entries - but not in the exact query string. So if I have a title called “this is an example search string”, the resulted additional query should return the list of the other 3 words (from the example query above):

I have thousands of entries in the database and the related words should be ordered by relevance (occurence) of course. Small words (under 3 chars) should be dropped.

Anyone can help me on this one?

When you get the title, you can remove matched words with preg_replace or something else.

Than you have rest of words from title and you can use it for related search.

Minimum word length is configured in mysql config file:

ft_min_word_len = 3;

It’s not a problem to run search like this:

…AGAINST (‘this is an’)… -> SQL will skip automatically words with length < 3, so you don’t need to do that manually.

If you do implement a minimum word length feature, make sure you give the user a way to override it, there are some times that you do need to search those words. I recently had this problem searching a forum for the phrase “The Big C”, it didn’t give me anything back because all the words are less than 3 chars. Maybe also allow Google style quoting of full string searches.