MySQL Fulltext search vs 'LIKE' on plurals etc

[pavsid]

Hi folks,

This topic has popped it's head up in various places on the internet but can't find anything on here.

Problem with the Fulltext search capability of MySQL is that it won't return results for search terms in the singular form if the DB contains only the plural form and vice versa.

E.g If DB contains 'opticians', and the search term is 'optician', then no results are returned.

When performing the same search using ..'LIKE %search_term%'.. then results are returned.

Similarly, a search for '%cloth%' will return no results using Fulltext, but will return results containing the word 'clothes' or 'clothing' if using a LIKE statement.

So, question is, is there a way to combine the two?

I've read about a function which checks for an 's' at the end of search terms and then splits it down into two words, one singular one plural, thereby searching for existance of both - but is this the only way to do it? That won't solve the '%cloth%' search problem.

Looking forward to your comments, thanks.

[pavsid]

Any offers on this peeps? before it goes to DatabaseJournal Forums?

[chazzy]

i think first you should show us your current query, so that maybe we can figure out why the full text search isn't working for singulars.

for example, if you are in fact using %cloth% as your full text search key, you might want to consider just 'cloth'

[Phill Pafford]

Maybe this link about Fulltext Boolean (http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html)

# 'apple*'

Find rows that contain words such as “apple”, “apples”, “applesauce”, or “applet”.

[pavsid]

i think first you should show us your current query, so that maybe we can figure out why the full text search isn't working for singulars.

for example, if you are in fact using %cloth% as your full text search key, you might want to consider just 'cloth'

Hi Chazzy,

The current query is this:

SELECT *,
MATCH(name, category, description, keywords) AGAINST ('$trimmed') AS score FROM businesses WHERE MATCH(name, category, description, keywords) AGAINST('$trimmed') ORDER BY score DESC";

...where $trimmed is basically trim($_GET['search'])

the same results occur using either '$trimmed' or '%$trimmed%'.

Hope that helps, many thanks

[pavsid]

Maybe this link about Fulltext Boolean (http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html)

# 'apple*'

Find rows that contain words such as “apple”, “apples”, “applesauce”, or “applet”.

Hi Phill, thanks for the link, i've had a look into the boolean search but as i understand this doesn't return a result in relevance order, like the normal full text search does - or am i wrong? If not, in your opinion, do you think it'll matter much when it comes to a search function like mine where there are only max 100 businesses to search through, the majority of which differ from one another anyway?

[Phill Pafford]

I might suggest REGEXP (http://www.techotopia.com/index.php/MySQL_Regular_Expression_Searches) with an OR condition


WHERE field_name REGEXP '[s]$'

[Shorts]

Hi Phill, thanks for the link, i've had a look into the boolean search but as i understand this doesn't return a result in relevance order, like the normal full text search does - or am i wrong? If not, in your opinion, do you think it'll matter much when it comes to a search function like mine where there are only max 100 businesses to search through, the majority of which differ from one another anyway?

You can order by relevancy in a fulltext search, you just need to set it up as a variable and then order by that.

e.g.:


$sql = mysql_query("SELECT *,MATCH(title,content,keywords) AGAINST ('$term' IN BOOLEAN MODE) AS relevance FROM `blogs` WHERE MATCH(title,content,keywords) AGAINST ('$term' IN BOOLEAN MODE) ORDER BY relevance DESC");


Also, you can set up seperate queries depending on null results. Building a search engine for the site I work for (talenttrove.com) and if the initial results turn up null there are then suggested search terms and removing filters.

[pavsid]

Thanks for all your time and help Phill.

i've tried the REGEXP method and have to say it works a treat! My query now looks like this:-

SELECT .....
WHERE (keywords) REGEXP '$trimmed' OR (description) REGEXP '$trimmed' OR (name) REGEXP '$trimmed'";


..is there a way to sort these results by relevancy?

[Phill Pafford]

Sorry for the late reply, could you elaborate on this more:


..is there a way to sort these results by relevancy?


are you looking for an ORDER BY ?

[pavsid]

are you looking for an ORDER BY ?

Hi Phill, yes, can you do that with a REGEXP search? I thought it only worked in conjunction with a MATCH.....AGAINST..... query?

Thanks again.