CODEWITHSUNDEEP
mysql
Arsen
Arsen

Reputation: 143

MySql misses short words in full text search query

I have table with a column 'on_phonetic' where phonetic keys for geographical coordinates stored. Value examples are: 'KF' (for Kiev), 'ASKRK, 'KNX SSP', etc

There is a fulltext index placed on this column. Query which I am launching:

SELECT * FROM osm_nodes 
WHERE MATCH(`on_phonetic`) AGAINST ('LSF');

it works ok for values with length greater than 2. but fails for values with length <= 2 so, this one will return nothing 

SELECT * FROM osm_nodes 
WHERE MATCH(`on_phonetic`) AGAINST ('KF');

however this query (not full-text) below returns tree records

SELECT * FROM osm_nodes 
WHERE on_phonetic = 'KF';

I made following updates in my.ini - file

ft_min_word_len=2
ft_stopword_file = ""

restarted server (ensured that my changes appeared in 'variables' section), rebuild indexes, even recreated table - nothing help. Any ideas ?

MySql version is 5.6.23-log, OS is Win7, x86_64

Thanks.

Upvotes: 0

Views: 112

Answers (2)

Java Dude
Java Dude

Reputation: 596

default db engine (i think you have InnoDB) doesn't support full text search so you need to use MyISAM db engine.

When to use MyISAM and InnoDB?

Upvotes: 1

Arsen
Arsen

Reputation: 143

Ha-ha, did not ever imagine that mysql full text works only with MyISAM engine.

Changing table type from InnoDB to myISAM resolved this issue

Upvotes: 0

Related Questions