Is it possible to stem full text match queries in MySql

Question

I'm trying to use wildcards to pass a stem of a word as part of a full text search in MySql. I would prefer to use match...against for the performance benefit instead of a like query.

I found this post which makes it sound as though this can be done: http://stackoverflow.com/questions/2062101/mysql-fulltext-with-stems

...but I can't get it to work for me.

My data looks like this:

table name: "rxnorm_brands"
step_medname              bn_name
Amoxicillin               Wymox
Amoxicillin-Clavulanate   Augmentin

This query works but uses "like":

select `step_medname`, `bn_name`
from `rxnorm_brands`
where (`bn_name` like 'Amox%' or `step_medname` like 'Amox%');

I want to use this query, but it returns nothing:

select `step_medname`, `bn_name`
from `rxnorm_brands`
where MATCH (`bn_name`, `step_medname`) AGAINST ('Amox*');

I do have a fulltext index on bn_name and step_medname. What am I doing wrong? Or can this not be done?

Answer 1

This can be done using IN BOOLEAN MODE, see: http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html.

So your query would become:

select `step_medname`, `bn_name`
from `rxnorm_brands`
where MATCH (`bn_name`, `step_medname`) AGAINST ('Amox*' IN BOOLEAN MODE);

but note that with the BOOLEAN MODE matching, rows either match or they don't - the results can no longer be ordered by relevance like they can with normal FULLTEXT searches.