MySQL fulltext search does not find existing record

21 Views Asked by At

I have found interesting issue within my MySQL fulltext search using MATCH function. Everything is fine except the records are incorrectly returned - only few of them.

Example:

There is a record in "song" table with "name" = 22 (only two characters)

a) When i use this MySQL query (LIKE) I get the record listed.

SELECT * FROM `song` WHERE name like '%22%';

b) When I use this MySQL query (MATCH) I get NO record.

SELECT * FROM `song` WHERE MATCH(`name`) AGAINST('*22*' IN BOOLEAN MODE ); 

I do not understand why the slower LIKE works like charm but the MATCH has the problem.

  1. There is a FULLTEXT index set on "name" column
  2. I tried to change ft_min_word_len variable to "2" but does not work.
  3. I work with MySQL 8.0.30, InnoDB tables type

Can anybody explain me, why I can't find the existing record with fulltext function?

Tahnks!

0

There are 0 best solutions below