MySQL FullText search on string shorter than 3 chars returns no rows

full-text-searchindexinnodbMySQL

I have a MySQL table with some addresses in it. Let's say I have the following 2 rows:

"10 Fake Street"
"101 Fake Street"

I'm trying to use fulltext search with MATCH() AGAINST(). I have set ft_min_word_len to 1, rebooted the server and dropped and then rebuilt the index by running

ALTER TABLE addresses DROP INDEX address_index
CREATE FULLTEXT INDEX address_index ON addresses(street)

I have verified that my ft_min_word_len is indeed set to 1 by running 

show global variables like 'ft_min_word_len'

If I include any word in my search that's shorter than 3 characters, I get no results back, unless I append a wildstar to it. For example

SELECT * FROM addresses WHERE MATCH(street) AGAINST('+101' IN BOOLEAN MODE)

or

SELECT * FROM addresses WHERE MATCH(street) AGAINST('+10*' IN BOOLEAN MODE)

both return 1 row "101 Fake Street". Running

SELECT * FROM addresses WHERE MATCH(street) AGAINST('+10' IN BOOLEAN MODE)

returns 0 rows. Why? The only suggestions I can find online all talk about setting min length, but I already verified that mine is set to 1 and rebuilt the index.

Best Answer

Setting ft_min_word_len only affects MyISAM.

You need to set innodb_ft_min_token_size to 1 since the default is 3.

Once you set innodb_ft_min_token_size to 1, go back and do

ALTER TABLE addresses DROP INDEX address_index
CREATE FULLTEXT INDEX address_index ON addresses(street);

Give it a Try !!!

Related Question