Mysql query: match against using wildcard

Question

I have table named 'acts' which has 3 columns which are indexed together:

act_name, short_description, main_description

Within the table, one row has an act named 'red riot'

When I execute the following search, red riot appears in the results:

SELECT * from acts where MATCH(act_name, short_description, main_description) AGAINST ('*r*' IN BOOLEAN MODE)

Yet if I expand on that, and search for *re* it returns no results:

SELECT * from acts where MATCH(act_name, short_description, main_description) AGAINST ('*re*' IN BOOLEAN MODE)

Why is that? Is there a better way to use wildcards in match against queries?

I have tried changing * to % but this returns no results on either query.

Answer 1

The value "re" is a stopword for MATCH() searches.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-stopwords.html

[edit]

It's true that "re" is a stopword, but the actual reason this wasn't working was because fulltext searching excludes words in the source material whose length is less than the value of the system variable ft_min_word_len (whose default value is 4).

So searching for "red*" will find records containing "redder" and "reddest", but not "red".

http://sqlfiddle.com/#!2/16d442/1