5

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.

1
  • @Mihai I'm InnoDB on MySQL 5.6.19
    – Egor
    Commented Sep 8, 2014 at 22:00

2 Answers 2

10

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

2
  • 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
    Commented Sep 8, 2014 at 22:38
  • You can also rebuild index by running: ``` USE db; OPTIMIZE TABLE tbl; ```
    – devope
    Commented Apr 23, 2022 at 16:03
0

I tried @RolandMySQLDBA solution, but still no results. The query is simple:

SELECT * FROM agreement a WHERE MATCH (a.title) AGAINST ('it*' IN BOOLEAN MODE)

The table is MyISAM, the title column has row with exact value: Systems IT, innodb_ft_min_token_size value is 1. The index was recreated. And no rows found... Any idea?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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