MySQL Fulltext match with forward slashes

full-text-searchMySQL

I'm trying to run a fulltext match against a product database, where the product names have forward slashes in them. These are staple sizes, so it's quite important that they get matched.

However, for every query I execute with a forward slash, I get 0 results.

The query I'm using is:

SELECT `product`.*
FROM `product`
WHERE MATCH (`product`.`name`) AGAINST('26/6')

I have also tried

SELECT `product`.*
FROM `product`
WHERE MATCH (`product`.`name`) AGAINST('\"26/6\"')

However, running the following returns 6 results, as expected:

SELECT `product`.*
FROM (`product`)
WHERE `product`.`name` LIKE '%26/6%'

The table is MyISAM and the product.name is varchar(255), with a FULLTEXT index on that column alone.
If I search using fulltext for something else, like 'tool', I get the results as expected, and the server is configured for 4 character or more matching.

What do I need to do to make this fulltext match work?

Best Answer

I suspect (with a high degree of of confidence) that the / isn't considered a "word" character. If that is the case, then Fine Tuning MySQL Full Text Search in the manual explains how to accomplish what you're wanting to do.

If you want to change the set of characters that are considered word characters, you can do so in several ways, as described in the following list. After making the modification, you must rebuild the indexes for each table that contains any FULLTEXT indexes. Suppose that you want to treat the hyphen character ('-') as a word character. Use one of these methods...

The second option offered there is probably the simplest, since you can apparently just edit the Character Definition Array in the appropriate XML file -- assuming you can use a one-byte character set such as latin1 for this column of this table. If you need utf8 or anything else multibyte, it apparently means recompiling the server from source after customizing the source to your specific needs.

Related Question