MySQL - Issue with FULLTEXT search with specific search value 'course'

Question

I have created below table.

CREATE TABLE `test` (
`name` VARCHAR(50) NOT NULL,
`id` INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
FULLTEXT INDEX `name` (`name`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

with following data

insert into test (name) values('apple');
insert into test (name) values('course');

and i am searching with following queries.

select * from test  where MATCH (name) AGAINST ('apple' IN BOOLEAN MODE);
select * from test  where MATCH (name) AGAINST ('course' IN BOOLEAN MODE);

Now the problem is that the first select query is returning the correct row. But the second query is not returning any rows. I tested with other words and they are working fine. But if the data has the word 'course' and when we search for 'course' it is not returning any rows.

Can someone help me out with this strange issue?

Answer 1

Isn't it best to use like operator?

select * from test  where name LIKE 'course';

Or even REGEXP

select * from test  where name RegexP 'course';

• SQLFIDDLE

You can try:

select * from test  where MATCH (name) AGAINST ('course*' IN BOOLEAN MODE);

It usually fails most in partial word searching though....So pleas show us some sample data from your table.

Bingo: It seems MYSQL is full of stop words.

So here is what is says and what you need to do :)

"As of MySQL 5.5.6, the stopword file is loaded and searched using latin1 if character_set_server is ucs2, utf16, or utf32. If any table was created with FULLTEXT indexes while the server character set was ucs2, utf16, or utf32, it should be repaired using this statement..

REPAIR TABLE tbl_name QUICK; "