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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Egor
  • 153
  • 1
  • 3

2 Answers2

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

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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 08 '14 at 22:38
  • You can also rebuild index by running: 
    USE db;
OPTIMIZE TABLE tbl;
    – devope Apr 23 '22 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?

long
  • 101
  • 3