MySQL FULLTEXT search (match against) not working

full-text-searchMySQL

CREATE TABLE `test` (
  `id` int(11) NOT NULL COMMENT 'unique id',
  `text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `text`) VALUES
(1288, 'i just don\'t know what to say.'),
(1340, 'so what?'),
(1344, 'that was probably what influenced their decision.'),
(1345, 'i\'ve always wondered what it\'d be like to have siblings.');

ALTER TABLE `test` ADD FULLTEXT KEY `text` (`text`);

select *, 
    match(text) against ('what' IN BOOLEAN MODE) as hasWhat,
    match(text) against ('just' IN BOOLEAN MODE) as hasJust
from test;

The match against "what" always returns 0 even though I'm using "IN BOOLEAN MODE." Why?

With MySQL 5.7.26 but it's also reproducible on http://sqlfiddle.com/#!9/c5c7f90/1 and https://sqltest.net/#1303145.

thanks

Best Answer

It's not working because "what" is a "stopword" ...

from https://dev.mysql.com/doc/refman/5.7/en/fulltext-stopwords.html

I followed the instructions and created a new stopword list.

create table test.custom_stopwords as
select * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD
where length(value) = 1;

SET GLOBAL innodb_ft_server_stopword_table = 'test/custom_stopwords';

Then I rebuilt the fulltext index and it worked with the word "what."

Related Question