There is a base of addresses with a full-text field.

In the field of type value:

Кировский р-н., Мира ул., 44б Кировский р-н., Мира ул., 40а Кировский р-н., Мира ул., 44а Кировский р-н., Мира ул., 42 

To search for addresses built query

 SELECT `id`, `street_id`, `object_id`, `search_string` FROM `addr_houses` WHERE MATCH(search_string) AGAINST("+мира +4*" IN BOOLEAN MODE) LIMIT 10 

The problem is that this query does not look for addresses that have a word shorter than 3 letters, for example, the above query does not select an address

 Кировский р-н., Мира ул., 42 

Also, it is impossible to get the addresses with the request "March 8", but for the "March" it is looking.

OS: Windows 10, DBMS - MariaDB 10.1, InnoDB table engine.

SHOW VARIABLES LIKE 'ft_min_word_len' that the matter is in the established minimum word length, looked like this: SHOW VARIABLES LIKE 'ft_min_word_len'

The value is 4.

Changed in the config to 1, restarted the service, the query began to show the value 1. Now we need to update the full-text index. Found on the Internet 2 options:

 1. REPAIR TABLE table_name QUICK; 2. OPTIMIZE TABLE table_name; 
  1. The storage engine for the table doesn't support repair
  2. I thought and thought and showed 2 messages Table does not support optimize, doing recreate + analyze instead and status = OK

But after these actions, the search still does not select lines with words shorter than 3 letters.

Amendment: A query like "peace" will find the line with house 42, but not "peace 4", so I don’t know how to formulate the problem accurately, I hope for understanding.

UPD 1:

I tried DROP, ADD

 ALTER TABLE addr_houses DROP INDEX `FULLTEXT_addr_houses_search_string`; ALTER TABLE addr_houses ADD FULLTEXT INDEX `FULLTEXT_addr_houses_search_string` (`search_string`); 

To make sure that this index is used after the deletion, it made a request, for which he received an error like "cant find fulltext index". After adding the index, the sample works in the same way as described above.

  • DROP INDEX / CREATE FULLTEXT INDEX - Akina
  • @Akina, I tried, it did not work = (Added details to the post - Skywave
  • After deleting and attempting to create an index, the server claims that there is no index. I think he is not lying. Then where did you look when you gave the command to create an index? What did the server say? what was the mistake? what warning? no need to shut my eyes, rather the opposite ... - Akina
  • @Akina, the error was when I deleted it, when I created it, everything became as before - Skywave
  • That is, now there is a PT index created with ft_min_word_len=1 , the current value is the same, the sought-for single-character "words" are not included in the stop list, but are still not searched? - Akina

0