MySQL Full Text Search Not Matching

Question

My MySQL table is not returning results with a MATCH (col) AGAINST ('') query.

The table is simple:

id | url | fullTextIndex

And my query is

SELECT *, Match(fullTextIndex) AGAINST ("7f7f7f807f8080807f8080807f7f7f807c828888808a86967e8b858d7f89838a76829e958f7badb68084a3a38384899077848b877f799f9c85799fa2827d8c8a ") FROM Pictures;

The last column, the match, is always 0. Except, I know for a fact that the string above is contained, verbatim, in one of the values.

Things to note:

• The string is only in that row (so it is not in more than 50% of rows, so it shouldn't be ignored).
• This is not the Full value
• The column is a bigText column
• When I use INSTR, I get the value 1 (which is correct)

Any ideas why this query might not be working?

Answer 1

There seems to be a (configurable) upper limitation on the length of the words considered for indexation:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_ft_max_word_len

You can check the current value with SHOW VARIABLES LIKE "ft_max_word_len";

It returns 84 on my server, and your string is 128 chars long.

Suggested fix:

1. Add this line to your my.cnf file: ft_max_word_len=128 (or whatever max length you need)

2. Rebuild your indexes as advised on the MySQL website: REPAIR TABLE tbl_name QUICK;