Marvin Marvin - 9 months ago 27
SQL Question

Is it possible to stem full text match queries in MySql

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:

...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?


This can be done using IN BOOLEAN MODE, see:

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.