mysql match against boolean mode

Question

I'm using MATCH AGAINST in boolean mode to add a simple search functionality to my site.

The column to be searched is varchar, with no full text index. The column value can be alphanumeric, all letters or all numbers, as well as certain combinations of both (eg. ABC-123').

There seems to be a few problems. First, the search doesn't work when there's a hyphen (like ABC-123). Second, it will not find partial matches. For a value of 856059, the searches look like this:

MATCH (field) AGAINST ('+856049' IN BOOLEAN MODE) // finds the record
MATCH (field) AGAINST ('+85604' IN BOOLEAN MODE) // does not find the record
MATCH (field) AGAINST ('+8560' IN BOOLEAN MODE) // does not find the record

I'm not sure why this is working this way. For various reasons I can't add a fulltext index to the column and use the "default" match against mode.

Answer 1

You may need to try some wildcard stuff

MATCH (field) AGAINST ('+85604*' IN BOOLEAN MODE)
MATCH (field) AGAINST ('+8560*' IN BOOLEAN MODE)

The MySQL Documentation says on '*':

*

The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it is appended to the word to be affected. Words match if they begin with the word preceding the * operator.

If a word is specified with the truncation operator, it is not stripped from a boolean query, even if it is too short or a stopword. Whether a word is too short is determined from the innodb_ft_min_token_size setting for InnoDB tables, or ft_min_word_len for MyISAM tables. The wildcarded word is considered as a prefix that must be present at the start of one or more words. If the minimum word length is 4, a search for '+word +the*' could return fewer rows than a search for '+word +the', because the second query ignores the too-short search term the.

Here is an example from the Documentation:

'apple*'

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

For more information, see MySQL Documentation on Fine Tuning Your Searches