Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
As I was falling back to a LIKE for < 4 character searches anyway (don't ask), I've add a check for character length of alphanumeric only. –  mattdwen Mar 7 '13 at 20:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.