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.