3

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.

  • @Mihai What do you mean? I have "10 Fake Street", where the first word is "10" – Egor Sep 8 '14 at 21:55
  • Are you on InnoDB or MyIsam? – Mihai Sep 8 '14 at 21:59
  • @Mihai I'm InnoDB on MySQL 5.6.19 – Egor Sep 8 '14 at 22:00
  • 1
    Here dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html it says something about ft_min_token_size and then do a repair. – Mihai Sep 8 '14 at 22:02
  • @Mihai Thanks a lot, that did the trick! You deserve the correct answer since you posted first, but sadly I can't accept comments. – Egor Sep 8 '14 at 22:40
6

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 !!!

  • 3
    Thanks! I guess since InnoDB only recently started supporting full text searches, most of the resources and answers online only mentioned the MyISAM variable. – Egor Sep 8 '14 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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