3

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?

Gray
  • 7,050
  • 2
  • 29
  • 52
user1285252
  • 127
  • 2
  • 8

2 Answers2

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';

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; "

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • RegexP is working but I have to use FULLTEXT search. Because i need to search words with in multiple columns (VARCHAR) having 2000 characters. I couldn't understand why this particular word 'course' is having issue. – user1285252 Jan 04 '13 at 10:19
  • [Check this out](http://stackoverflow.com/questions/4612787/mysql-fulltext-search-not-working-for-some-words-like-house). It's not a matter of only `course`...it seems. – bonCodigo Jan 04 '13 at 10:22
  • 1
    Thank You. The reason for the issue is course being mysql stop word. – user1285252 Jan 04 '13 at 10:24
  • I am going to give you a +1 coz I was really having this issue but couldn't figure out :) – bonCodigo Jan 04 '13 at 10:27
0

Add "IN BOOLEAN MODE" in your AGAINST query
LIKE AGAINST ('course*' IN BOOLEAN MODE) This will perfectly works

Ajay Gadhavana
  • 415
  • 5
  • 5