0

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.

Egor
  • 1,622
  • 12
  • 26
  • This question might better be asked on **dba.stackexchange.com**. – spencer7593 Sep 08 '14 at 21:21
  • @spencer7593 You are entirely correct! I've gotten so used to SO that I didn't even think about other stack exchanges when asking. My apologies, I'll go post there. – Egor Sep 08 '14 at 21:24
  • @spencer7593 I do have that, I have "10 Fake Street", which contains "10" as a word. – Egor Sep 08 '14 at 21:35
  • Yes, I re-read your question and saw that you did; and deleted my comment. – spencer7593 Sep 08 '14 at 21:35
  • This was marked as duplicate, but the duplicate answer is "Set ft_min_word_len and then restart and rebuild index". I explicitly stated that I did that and it didn't work for me. – Egor Sep 08 '14 at 22:02
  • Yes, you did. But someone wielding a duplicate hammer has pounded down a decision that this question should be closed. Maybe you could re-ask the question with a different title. Or maybe the folks over on the dba.stackexchange.com site will be more receptive. – spencer7593 Sep 08 '14 at 22:08
  • The dba folks came through. This variable is for MyISAM databases, while I have InnoDB, so I should be using innodb_ft_min_token_size. Until recently InnoDB didn't support full text searches, so there are very few mentions of this variable when you search for the problem. – Egor Sep 08 '14 at 22:36
  • Ahhh, I assumed you were using MyISAM. The use of FULLTEXT with InnoDB wasn't introduced until MySQL 5.6. – spencer7593 Sep 08 '14 at 22:42

0 Answers0