0

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.

Arsen
  • 153
  • 1
  • 12
  • with only 2 chars of fulltext indexing, you may be running into many more things showing up as "noise" words due to appearing in 50%+ of results. Anything mysql considers noise wouldn't get returned as a result. – Marc B Jun 01 '15 at 16:32
  • Marc B, is there any possibility to 'turn off' this 'node' feature ? – Arsen Jun 01 '15 at 17:24

2 Answers2

1

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?

Community
  • 1
  • 1
Java Dude
  • 454
  • 6
  • 24
0

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

Arsen
  • 153
  • 1
  • 12